Help with SUMPRODUCT

[Alex Mackenzie]

I admit I do not fully understand SUMPRODUCT; I am trying to follow the
variious samples I have found.

Data:

Whs Reasons Returns
1 1 2
10 DF 1
1 2 1

I want to sum the number of Returns when Whs=1 and Reasons=1, where Whs,
Reasons and Returns are all named ranges.

I have "=SUMPRODUCT((Whs=1)*AND(Reasons=1)*(Returns)) but the result is 0
rahter than 2.

Any help would be greatly appreciated.

Thank you.

 

[Jacob Skaria]

=SUMPRODUCT((Whs=1),(Reasons=1),(Returns))

 

[Jacob Skaria]

=SUMPRODUCT(--(Whs=1),--(Reasons=1),(Returns))

eg
=SUMPRODUCT(--(A2:A6=D2),--(B2:B6=D3),(C2:C6))

 

[Alex Mackenzie]

Used the formula as written, results in #VALUE!.

I believe my ranges are okay, since I have summed the returns based on
reasons only; I want to breakdown the results further by Whs.

Thank you.

 

[Francis]

try this

=SUMPRODUCT(--(Whs=1),--(Reasons=1),C2:C4)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Alex Mackenzie]

Sorry, same result. The change was 2 hyphens correct?

Does the fact that I am stuck with Excel 2000 have anything to do with it?

 

[Jacob Skaria]

Try without the named ranges and then post your formula if it doesnt.

 

[Dave Peterson]

Are all the ranges the same size (and single columns)?

And are all the ranges less than a whole column?

Are there any errors in any of the ranges?

If this doesn't help, what are the addresses for each of the ranges?