Prev: document doesn't auto display when selected
Next: Deactivate a macro when used and reactivate again
From: ram on 23 Apr 2010 10:54
Hi All,

Is it possible to use concatenation with multiple sumif?

I have the following formula but it doesn't work with the concatenation, any
suggestion of what I'm doing wrong

Thanks in advance for any help


=SUM(IF((tblProcessorActivity!$B$1:$B$2999="CG")*(tblProcessorActivity!$N$1:$N$2999=(MONTH(MAX(tblProcessorActivity!$C:$C))&RIGHT('Processor
Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$2999))
From: T. Valko on 23 Apr 2010 16:21
There's nothing wrong with the formula syntax so you'll have to explain in
more detail what "doesn't work" means.

--
Biff
Microsoft Excel MVP


"ram" <ram(a)discussions.microsoft.com> wrote in message
news:8AEC733D-8EB0-4C41-96D3-7EFEC0B45F44(a)microsoft.com...
> Hi All,
>
> Is it possible to use concatenation with multiple sumif?
>
> I have the following formula but it doesn't work with the concatenation,
> any
> suggestion of what I'm doing wrong
>
> Thanks in advance for any help
>
>
> =SUM(IF((tblProcessorActivity!$B$1:$B$2999="CG")*(tblProcessorActivity!$N$1:$N$2999=(MONTH(MAX(tblProcessorActivity!$C:$C))&RIGHT('Processor
> Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$2999))


From: ram on 23 Apr 2010 17:53
Hi
It doesn't sum any numbers because in the critera It needs to compare the
week number and month. If i use only the month comparison I get a result of 2
which is correct. When I use the concatenation and right function it returns
zero. When I just use the right function for the criteria it also only
returns zero but it should return 2 in all these examples.

Thanks for your help



"T. Valko" wrote:

> There's nothing wrong with the formula syntax so you'll have to explain in
> more detail what "doesn't work" means.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "ram" <ram(a)discussions.microsoft.com> wrote in message
> news:8AEC733D-8EB0-4C41-96D3-7EFEC0B45F44(a)microsoft.com...
> > Hi All,
> >
> > Is it possible to use concatenation with multiple sumif?
> >
> > I have the following formula but it doesn't work with the concatenation,
> > any
> > suggestion of what I'm doing wrong
> >
> > Thanks in advance for any help
> >
> >
> > =SUM(IF((tblProcessorActivity!$B$1:$B$2999="CG")*(tblProcessorActivity!$N$1:$N$2999=(MONTH(MAX(tblProcessorActivity!$C:$C))&RIGHT('Processor
> > Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$2999))
>
>
> .
>
From: T. Valko on 23 Apr 2010 20:10
>=SUM(IF((tblProcessorActivity!$B$1:$B$2999="CG")*(tblProcessorActivity!$N$1:$N$2999=(MONTH(MAX(tblProcessorActivity!$C:$C))&RIGHT('Processor
Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$2999))

Ok, I'm assuming that tblProcessorActivity!$C:$C contains dates.

You're getting the month number of the max date:

=(MONTH(MAX(tblProcessorActivity!$C:$C))

Then yo're concatenating that with:

&RIGHT('Processor Time Allocation'!B$6,1)

So, what's in 'Processor Time Allocation'!B$6 ?

--
Biff
Microsoft Excel MVP


"ram" <ram(a)discussions.microsoft.com> wrote in message
news:7F810D3C-F542-484A-B7DC-C50EE1519C78(a)microsoft.com...
> Hi
> It doesn't sum any numbers because in the critera It needs to compare the
> week number and month. If i use only the month comparison I get a result
> of 2
> which is correct. When I use the concatenation and right function it
> returns
> zero. When I just use the right function for the criteria it also only
> returns zero but it should return 2 in all these examples.
>
> Thanks for your help
>
>
>
> "T. Valko" wrote:
>
>> There's nothing wrong with the formula syntax so you'll have to explain
>> in
>> more detail what "doesn't work" means.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "ram" <ram(a)discussions.microsoft.com> wrote in message
>> news:8AEC733D-8EB0-4C41-96D3-7EFEC0B45F44(a)microsoft.com...
>> > Hi All,
>> >
>> > Is it possible to use concatenation with multiple sumif?
>> >
>> > I have the following formula but it doesn't work with the
>> > concatenation,
>> > any
>> > suggestion of what I'm doing wrong
>> >
>> > Thanks in advance for any help
>> >
>> >
>> > =SUM(IF((tblProcessorActivity!$B$1:$B$2999="CG")*(tblProcessorActivity!$N$1:$N$2999=(MONTH(MAX(tblProcessorActivity!$C:$C))&RIGHT('Processor
>> > Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$2999))
>>
>>
>> .
>>


From: Dave Peterson on 24 Apr 2010 07:31
Biff,

In a different thread the OP said that it was text in the header that was
causing the =month() portion to fail.

Although, the formula changed, too.

=SUMPRODUCT((tblProcessorActivity!B1:B30000="CG")
*(MONTH(tblProcessorActivity!C1:C30000)=MONTH(MAX(tblProcessorActivity!C:C)))
*(tblProcessorActivity!N1:N30000=4),
tblProcessorActivity!D1:D30000)



"T. Valko" wrote:
>
> >=SUM(IF((tblProcessorActivity!$B$1:$B$2999="CG")*(tblProcessorActivity!$N$1:$N$2999=(MONTH(MAX(tblProcessorActivity!$C:$C))&RIGHT('Processor
> Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$2999))
>
> Ok, I'm assuming that tblProcessorActivity!$C:$C contains dates.
>
> You're getting the month number of the max date:
>
> =(MONTH(MAX(tblProcessorActivity!$C:$C))
>
> Then yo're concatenating that with:
>
> &RIGHT('Processor Time Allocation'!B$6,1)
>
> So, what's in 'Processor Time Allocation'!B$6 ?
>
> --
> Biff
> Microsoft Excel MVP
>
> "ram" <ram(a)discussions.microsoft.com> wrote in message
> news:7F810D3C-F542-484A-B7DC-C50EE1519C78(a)microsoft.com...
> > Hi
> > It doesn't sum any numbers because in the critera It needs to compare the
> > week number and month. If i use only the month comparison I get a result
> > of 2
> > which is correct. When I use the concatenation and right function it
> > returns
> > zero. When I just use the right function for the criteria it also only
> > returns zero but it should return 2 in all these examples.
> >
> > Thanks for your help
> >
> >
> >
> > "T. Valko" wrote:
> >
> >> There's nothing wrong with the formula syntax so you'll have to explain
> >> in
> >> more detail what "doesn't work" means.
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "ram" <ram(a)discussions.microsoft.com> wrote in message
> >> news:8AEC733D-8EB0-4C41-96D3-7EFEC0B45F44(a)microsoft.com...
> >> > Hi All,
> >> >
> >> > Is it possible to use concatenation with multiple sumif?
> >> >
> >> > I have the following formula but it doesn't work with the
> >> > concatenation,
> >> > any
> >> > suggestion of what I'm doing wrong
> >> >
> >> > Thanks in advance for any help
> >> >
> >> >
> >> > =SUM(IF((tblProcessorActivity!$B$1:$B$2999="CG")*(tblProcessorActivity!$N$1:$N$2999=(MONTH(MAX(tblProcessorActivity!$C:$C))&RIGHT('Processor
> >> > Time Allocation'!B$6,1))),tblProcessorActivity!$D$1:$D$2999))
> >>
> >>
> >> .
> >>

--

Dave Peterson
 |  Next  |  Last
Pages: 1 2
Prev: document doesn't auto display when selected
Next: Deactivate a macro when used and reactivate again