sum a range with multiple criteria

[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

 

[Teethless mama]

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

 

[Ragdyer]

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)

 

[T. Valko]

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)

 

[Dianne]

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

 

[Ragdyer]

You're welcome, and appreciate the feed-back.