Sumproduct in Excel question 2

[yalamo]

I am trying to get the sumproduct of a certain type of cells. I have the following cellS:

Column: A B C
Row
3 10 8 D
4 20 7 B
5 30 6 S
6 40 5 D
7 50 4 B
8 60 3 S
9 70 2 D
10 80 1 B

If in another cell I use the formula:
=SUMPRODUCT((A3:A10)*(B3:B10)*(IF(C3:C10="D",1,0)))
I get #VALUE! Can someone tell me what I'm doing wrong, and how to get it right?

 

[SkipVought]

Hi,

Enter it as an ARRAY FORMULA

ctr+shift+enter

Skip,

_[red][/red]

 

[Rofeu]

Replace

(IF(C3:C10="D",1,0))

with

 --(C3:C10="D")

Cheers,

Roel

 

[AnotherHiggins]

It seems to me that your formula could be more easily stated as
[tab][COLOR=blue white]=SUMPRODUCT((A3:A10)*(B3:B10)*(C3:C10="D"))[/color]

That would not need to be entered as an array formula. That is, just pressing enter would allow it to return the same answer.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Perfect, John!

Skip,

_[red][/red]

 

[yalamo]

Thanks for the answer. anotherhiggins & Rofeu I used your solution. Seems simpler than using an array formula.

Thanks again to all of you.

 

[Rofeu]

Hi,

although anotherhiggins' solution will work, it's better to put the double negative (--) in front of your 'criteria'. This coerces the returned boolean to an integer.

If you leave it out, Excel converts it for you, which takes just a little bit longer. (no probs if you have just one formula, but a rather major effort if you've for example 10k of them)

Cheers,

Roel

 

[AnotherHiggins]

I never knew that it saved time, Roel. Thanks!

->

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[yogia]

To add to John and Roel's ontributions, also ...

=SUMPRODUCT((A3:A10),(B3:B10),--(C3:C10="D"))



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[xlhelp]

Roel,

John gave you a star in the reply, but not in the thread. So, I will make up for it.

Member- AAAA Association Against Acronym Abusers

 

[xlhelp]

Then again, I may be mistaken. I am getting to that stage... you know.

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

No, you're right. I typed that I was going to give a star, then wandered off to lunch. I just realized my mistake around the same time you were probably hitting "Thank Rofeu
for this valuable post".

Well, you get a twofer, Roel.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Rofeu]

Thanks, guys!