sum a range with multiple criteria
 

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

sum a range with multiple criteria
 

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


"Dianne" wrote:

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

sum a range with multiple criteria
 

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)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Dianne" wrote in message
...
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

sum a range with multiple criteria
 

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)

--
Biff
Microsoft Excel MVP


"Dianne" wrote in message
...
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

sum a range with multiple criteria
 

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" wrote:

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)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Dianne" wrote in message
...
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

sum a range with multiple criteria
 

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dianne" wrote in message
...
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" wrote:

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)
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Dianne" wrote in message
...
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