Multiple (3) SUMPRODUCT conditions

**timjames** · 11-03-2011, 02:05 PM

Hi

I'm trying to SUMPRODUCT multiple criteria but am obviously doing something wrong.

It needs to be a SUMPRODUCT as the source data will be stored elsewhere and will not be open at the same time as the COSTS sheet. I don't need a pivot table either, for other reasons.

Essentially what I need is...

In cell E7 of the costs sheet, put the total Spend from column O on the source sheet...

IF,

The Project in Source column B = COSTS E1 - and
The Category in Source column P = COSTS B7 - and
The Month in Source column Q = COSTS E4

So, the total Associates spend for Project1 in January

I'm currently using this formula but getting a zero (not N/A or VALUE)
=SUMPRODUCT((source!B:B=COSTS!E1)*(source!P:P=COSTS!B7)*(source!Q:Q=COSTS!E4),(source!O:O))

Your help would be hugely appreciated as always.

Many thanks in advance

Tim

**NBVC** · 11-03-2011, 02:10 PM

Source sheet, Q:Q has "January", while E4 in the Costs sheet has "Jan". They should be the same.

Also, it is not recommended to use whole column references with SUMPRODUCT as it is processor intensive..

**timjames** · 11-03-2011, 03:17 PM

Originally Posted by NBVC

Source sheet, Q:Q has "January", while E4 in the Costs sheet has "Jan". They should be the same.

Also, it is not recommended to use whole column references with SUMPRODUCT as it is processor intensive..

Thank you. There's always a simple answer!

Will reduce the columns too to a specific range.

Thanks for the tip.