conditional sum formula

[Guest]

I have a list of numbers to add if they meet two criteria and I have been
trying to use the conditional sum feature in excel.

Column A is a list from 1 to 5 and I want to sum all the numbers in column B
if Column A is either a 1 or a 5.

Below is the array formula the conditional sum feature produces and it
returns 0 when it should return 11. The array works if there is only one
variable but not multiple.

Can someone tell me why its not working?

Thank you.

{=SUM(IF($A$3:$A$10=5,IF($A$3:$A$10=1,$A$3:$A$10,0),0))}

2 2
5 5
2 2
1 2
5 5
1 2
2 2
1 2
2 2

 

[Guest]

Oops, I see my formula adds column A when it should be column B. My mistake!
But the problem is the same. The formula doesn't handle multiple criteria.
It came out of the conditional sum add in from excel so I don't understand
why it wouldn't. But I am willing to get over that and just write the
formula myself. (if I knew how)

 

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{1,5},0))),B1:B10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ardus Petus]

=SUMPRODUCT(($A$3:$A$10=5)+($A$3:$A$10=1),$B$3:$B$10)

This should do it.

HTH