Multiple Sumif with concatenation

  • Thread starter Thread starter ram
  • Start date Start date
R

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))
 
There's nothing wrong with the formula syntax so you'll have to explain in
more detail what "doesn't work" means.
 
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
 
=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,

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

Thanks, Dave!

--
Biff
Microsoft Excel MVP


Dave Peterson said:
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)
Click to expand...
 
Back
Top