Conditional Sums Using Multiple Criteria

  • Thread starter Thread starter PeterLines
  • Start date Start date
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.
 
Did you array enter it without the curly braces by using CTRL+SHIFT+ENTER at the
same time. Works fine for me.
 
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)
 
Back
Top