sum a range with multiple criteria

  • Thread starter Thread starter Dianne
  • Start date Start date
D

Dianne

I want to sum the numbers in column c if the information in column a and b is
to my selection. There are multiple categories for both column a and b, but I
want to select only "cps" for column A and "travel" for column B. When those
to conditions are met I want to add the values in column C. I can't get the
formula below to respond with anything other than #value! or #name?
=SUM(IF(A1:A8="cps", ANDIF(B1:B8="travel",C1:C8)))
Payer cost type total
cps vendor 33
us vendor 22
cps travel 24
us travel 20
us software 19
cps travel 30
 
your formula is very close to work, just remove the " AND" part
=SUM(IF(A1:A8="cps", ANDIF(B1:B8="travel",C1:C8)))

here is the correction:
=SUM(IF(A1:A8="cps",IF(B1:B8="travel",C1:C8)))

makesure press ctrl+shift+enter, not just enter
 
You were close.
You should realize that your formula was an *array* formula and therefore
requires an array entry using CSE.

=SUM(IF((A1:A8="cps")*(B1:B8="travel"),C1:C8))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

You could also use this type formula which does *not* require a CSE entry:

=SUMPRODUCT((A2:A7="cps")*(B2:B7="travel")*C2:C7)
 
Try this:

=SUMPRODUCT(--(A1:A8="cps"),--(B1:B8="travel"),C1:C8)

Better to use cells to hold the criteria:

E1 = cps
F1 = travel

=SUMPRODUCT(--(A1:A8=E1),--(B1:B8=F1),C1:C8)
 
Wow! This is sooo great! It works perfect. I really appreciate that there is
this resource available to help with formulas. This works so great. I used
the =SUM(IF((A1:A8="cps")*(B1:B8="travel"),C1:C8)) with CSE and the result
was 54, exactly what I was looking for. Thanks a million times over!
Dianne
 
Back
Top