SUM IF 2 CONDTIONS

[Guest]

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

 

[SteveG]

You could use either

=SUMPRODUCT(--(P25="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

 

[Guest]

thanks,

but what is this symbol?

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[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

 

[Guest]

Thank you both very much! This has been extremely helpful.

ellebelle