Conditional Sums Using Multiple Criteria

P

PeterLines

I want to sum a column if 2 conditions in a row are true. For example:
If I have an array with the columns: Name, Type, Amount. I only want
to sum Amount if Name="Pete" and Type="A". I've tried
{=sum((Name="Pete")*(Type="A")*Amount)} I found this formula the book
Microsoft Excel 2000 Formulas page 204 but it does not seem to work.
Any help is appreciated.
 
K

Ken Wright

Did you array enter it without the curly braces by using CTRL+SHIFT+ENTER at the
same time. Works fine for me.
 
B

Bob Phillips

Peter,

That is an array formula, so you the {...} are not typed, they are put in by
Excel when you put the formula, entering with Ctrl-Shift-Enter rather than
just Enter.

You could also try

=SUMPRODUCT((Name="Pete")*(Type="A"),(Amount))

but the named ranges cannot be whole columns with SUMPRODUCT

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top