SUM IF 2 CONDTIONS

E

ellebelle

Hi there,

I have a little probelm with my excell formula. I would like to do the
following:

If values in column R are equal to "Forest" AND if values in column P are
equal to "Peter" then sum the numerical values in column AN.

Any help would be greatly appreciated!

ellenbelle
 
S

SteveG

You could use either

=SUMPRODUCT(--(P2:p5="Forest"),--(R2:R5="Peter"),(AN2:AN5))

or an array formula.

{=SUM(IF($P$2:$P$5="Forest",IF($R$2:$R$5="Peter",$AN$2:$AN$5,0),0))}

If you use the array, don't type in the {}curly brackets. Type th
formula and commit with Ctrl-Shift-Enter rather than just Enter. Thi
will automatically put the curly brackets around it.

HTH

Stev
 
S

SteveG

It is called the double unary operator. It creates a TRUE/FALSE array
for the range you are evaluating. When you are evaluating 2 ranges of
conditions, if both are met then TRUE*TRUE = 1. Sumproduct then
multiplies that 1 by the number in the column you are summing based on
the conditions. This website can answer your questions in more
detail.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
 
Top