Sumif to SumProduct

**ggremel** · 11-16-2010, 12:30 PM

I converting a formula from

=SUMIF('[2011 Consolidated Master Allocation.xls]PR Rates'!$N$5:$N$478,V99,'[2011 Consolidated Master Allocation.xls]PR Rates'!$P$5:$P$478)

Result = 506

to

=SUMPRODUCT(('[2011 Consolidated Master Allocation.xls]PR Rates'!$N$5:$N$1000=V99),'[2011 Consolidated Master Allocation.xls]PR Rates'!$P$5:$P$1000))

Result = 0

Can anyone help me identify why I am getting a zero on the above sum product formula?

**zbor** · 11-16-2010, 12:37 PM

Try add -- in your formula:

=SUMPRODUCT(--('[2011 Consolidated Master Allocation.xls]PR Rates'!$N$5:$N$1000=V99),'[2011 Consolidated Master Allocation.xls]PR Rates'!$P$5:$P$1000))

or remove , from it nad multiply:

=SUMPRODUCT(('[2011 Consolidated Master Allocation.xls]PR Rates'!$N$5:$N$1000=V99)*'[2011 Consolidated Master Allocation.xls]PR Rates'!$P$5:$P$1000))

**sailepaty** · 11-16-2010, 01:15 PM

If there is just one condition to evaluate, why you want to move from SUMIF to SUMPRODUCT?

Regards

**DonkeyOte** · 11-16-2010, 01:19 PM

SUMIF does not work with closed targets - presumably OP has the file open while creating the formulae (shorthand link) but will close thereafter ?

**sailepaty** · 11-16-2010, 01:30 PM

Thanks Don, I didn't think about that.

Regards

**ggremel** · 11-16-2010, 01:43 PM

I had not tired the multiplication (*) before. That is a great way to solution this challenge and provide a formula most people could follow.

Thank you.