sum a column based on multiple other columns being true

[sofia]

I am trying to sum amounts based on meeting multiple criteria in other
colums. I saw the response to the "contruction guy" posted here, but it does
not work for my information?

Month Client Amt
Jul Smith 5
Jul Smith 2
Aug Smith 3
Aug Scott 10

I want to calculate total amount per client each month using "sum" and "if"
statements.

Thank you,

sofia

[Bob Phillips]

=SUMPRODUCT(--(A2:A20="Jul"),--(B2_B20="Smith"),C2:C20)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sofia" <sofia@discussions.microsoft.com> wrote in message
news:AECB3707-780C-4D0E-818A-FC153096484C@microsoft.com...
> I am trying to sum amounts based on meeting multiple criteria in other
> colums. I saw the response to the "contruction guy" posted here, but it
does
> not work for my information?
>
> Month Client Amt
> Jul Smith 5
> Jul Smith 2
> Aug Smith 3
> Aug Scott 10
>
> I want to calculate total amount per client each month using "sum" and
"if"
> statements.
>
> Thank you,
>
> sofia

[Ken]

Sofia

You can use a multiple criteria sum function (array function) like:

{=SUM(($A$2:$A$5="Jul")*($B$2:$B$5="Smith")*$C$2:$C$5)}

This is entered as an array function (shift-ctl-enter). Athough, a
sumif function looks intuitively like something that should work, it
does not work with multiple criteria as you a have.

Good luck.

Ken
Norfolk, Va



sofia wrote:
> I am trying to sum amounts based on meeting multiple criteria in other
> colums. I saw the response to the "contruction guy" posted here, but it does
> not work for my information?
>
> Month Client Amt
> Jul Smith 5
> Jul Smith 2
> Aug Smith 3
> Aug Scott 10
>
> I want to calculate total amount per client each month using "sum" and "if"
> statements.
>
> Thank you,
>
> sofia

[Tom Hutchins]

Why not use a pivot table? Put the Month as a page field or row field, put
the Client as a row field, and put the Amt as the data field.

If you don't want to use a pivot table, you will have to set up a separate
formula for each combination of criteria:

=SUMPRODUCT(--(A2:A5="Jul"),--(B2:B5="Smith"),C2:C5)
=SUMPRODUCT(--(A2:A5="Aug"),--(B2:B5="Smith"),C2:C5) etc.

Hope this helps,

Hutch

"sofia" wrote:

> I am trying to sum amounts based on meeting multiple criteria in other
> colums. I saw the response to the "contruction guy" posted here, but it does
> not work for my information?
>
> Month Client Amt
> Jul Smith 5
> Jul Smith 2
> Aug Smith 3
> Aug Scott 10
>
> I want to calculate total amount per client each month using "sum" and "if"
> statements.
>
> Thank you,
>
> sofia

[sofia]

Wow, you are great! I want to use this number in a separate worksheet so I
rather have the formula than a pivot table, but I will try both ways to see
what works better for me.

Thank you,

sofia

"Tom Hutchins" wrote:

> Why not use a pivot table? Put the Month as a page field or row field, put
> the Client as a row field, and put the Amt as the data field.
>
> If you don't want to use a pivot table, you will have to set up a separate
> formula for each combination of criteria:
>
> =SUMPRODUCT(--(A2:A5="Jul"),--(B2:B5="Smith"),C2:C5)
> =SUMPRODUCT(--(A2:A5="Aug"),--(B2:B5="Smith"),C2:C5) etc.
>
> Hope this helps,
>
> Hutch
>
> "sofia" wrote:
>
> > I am trying to sum amounts based on meeting multiple criteria in other
> > colums. I saw the response to the "contruction guy" posted here, but it does
> > not work for my information?
> >
> > Month Client Amt
> > Jul Smith 5
> > Jul Smith 2
> > Aug Smith 3
> > Aug Scott 10
> >
> > I want to calculate total amount per client each month using "sum" and "if"
> > statements.
> >
> > Thank you,
> >
> > sofia