Solved: SumProduct

[Philcjr]

Need some help here with SumProduct, I keep getting #NUM... when you open the file, hopefully it should be self explanitory.

Any questions, please let me know.

Phil

Oh ya, I have read XLD's lesson on SumProduct over and over and its just not sinking in

[Bob Phillips]

I see 3 things wrong.

First, you cannot use whole columns in SP pre Excel 2007, you have to use an explicit range.

Second, you should be testing column C for the product, not D.

Third, column E is a number, not text

=SUMPRODUCT(('Pivot Table'!C5:C132=A3)*('Pivot Table'!E5:E132>0.01))

[Philcjr]

I tried your formla and it did not work, I got a value of "0" when it should be "7" for Cell B3

[Bob Phillips]

There seems to be something amiss with your pivot. I can only see two BF01 items in the source data, but the pivot shows 5 items. But none of these are for BPCK-BONUS-PACK, so the result is 0 as it should be. The seven refer to a different type, BV31, if you filter the pivot to an item class of BPCK-BONUS-PACK

[Philcjr]

I think I can code this faster than I could do a formula...

You say you can only see two "BF01" items, what is a BF01?

Do you have any other suggestions I could try?

[Philcjr]

This works:
=SUMPRODUCT(('Pivot Table'!C5:C132=A3)*('Pivot Table'!D5:132>0.01))

Thanks!

[Bob Phillips]

That is the plant.

My suggestion, run the SP against the sourcedata. Agaisnt the pivot is fine until you filter the pivot, then the data is column E refers to a different plant than another time.