SUMIF w/ Multiple criteria??

  • Thread starter Thread starter Jana
  • Start date Start date
J

Jana

I have an excel spreadsheet with columns set up as
follows. I want to do a SUMIF formula on another
spreadsheet that links to this spreadsheet with mulitple
criteria. See sample below: I want to sum the totals in
Column C IF Column A = BOB AND Column B = Task #1 so that
the cell will equal 90.
COLUMN A COLUMN B COLUMN C
BOB Task #1 40
BOB Task #2 32
BOB Task #1 50
BOB Task #2 40

I've set up a SUMIF formula based on one criteria, but
don't know how to add a second criteria to that.
PLEASE HELP!
 
Hi
try
=SUMPRODUCT((A1:A100="BOB")*(B1:B100="Task#1),C1:C100)

or as alternative syntax
=SUMPRODUCT(--(A1:A100="BOB"),--(B1:B100="Task#1),C1:C100)

Note: SUMPRODUCT does not allow ranges like B:B (as SUMIF does)
 
Frank (and others)
I see this syntax with double minus sign in connection with the SUMPRODUCT function again and again. Earlier Chip Pearson explained
to me what it all means, but I wonder - is there actually convincing evidence that it has any noticeable effect?

Hans Knudsen
 
Hi Hans
besides a small performance benefit (only noticable if you have many
SUMPRODUCT formulas with large ranges) you may take a look at the
following discussion:
http://tinyurl.com/22bqx

Harlan Grove gave some additional reasons (operator preference) for
using the unary operator
 
Back
Top