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


"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

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

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

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

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