Multiple Sumif with concatenation

[ram]

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))

 

[T. Valko]

There's nothing wrong with the formula syntax so you'll have to explain in
more detail what "doesn't work" means.

 

[ram]

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]

=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 ?

 

[Dave Peterson]

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!D130000)

 

[T. Valko]

OK

Thanks, Dave!

--
Biff
Microsoft Excel MVP

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!D130000)