conditional sum formula
 

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

conditional sum formula
 

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)

"Todd" wrote:

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

conditional sum formula
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Todd" wrote in message
...
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

conditional sum formula
 

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

This should do it.

HTH
--
AP


"Todd" a écrit dans le message de
...
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)

"Todd" wrote:

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