SUM IF with AND and OR

[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))

 

[immanuel]

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.

 

[Arvi Laanemets]

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

 

[immanuel]

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.

 

[Don Guillett]

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

 

[Kelly]

Worked like a charm ... thank you very much. (I was short
on the number of (()) needed).