SUM IF with AND and OR

  • Thread starter Thread starter Kelly
  • Start date Start date
K

Kelly

I have one definite criteria, but the second can be one of
two options. Is it possible to use to AND and OR
functions in a SUM IF formula?
=SUM(IF(+$A$10:$A$151="db"*$C$10:$C$151=("Y","")1,0))
 
You could try:

{=SUM(IF(($A$10:$A$151)*(($C$10:$C$151="Y")+($C$10:$C$151="")),1,0))}

That is to say, force your order of operations with parentheses.

/i.
 
Hi

Your formula doesn't have any meaning at all - brackets are in wrong places,
delimiters are in wrong places, and what is a SUM IF formula? There is a
SUMIF ()formula, which works witht single condition, and there are formulas
SUM() and IF().

What do you want? To count simoltaneous occurences of "db" in range
$A$10:$A$151 and "Y" in range $C$10:$C$151? Then use the formula
=SUMPRODUCT(($A$10:$A$151="db")*($C$10:$C$151="Y"))
Or you want to sum values in third column, when both conditions are true?
Then the formula will be something like
=SUMPRODUCT(($A$10:$A$151="db")*($C$10:$C$151="Y")*($D$10:$D$151))
when values to sum are in column D.


Arvi Laanemets
 
Shucks. Sorry for the error. This should be:

{=SUM(IF(($A$10:$A$151="db")*(($C$10:$C$151="Y")+($C$10:$C$151="")),1,0))}

/i.
 
maybe
=sumproduct((a10:a151="db")*(c10:c151="Y"))
will COUNT those that meet the criteria
=sumproduct((a10:a151="db")*(c10:c151="Y")*d10:d151)
will sum range d for the conditions in a and c
 
Back
Top