sum based on 2 criteria within a table
 

I am trying to do a SUMIF on a table, but I have multiple criteria in 2
columns.

The table is such that the name repeats several times, as there is another
column with account numbers in it, which in most cases also has multiple
lines. The last column holds dollar amounts.

I want to sum the dollar amounts for a specified name/account number
combination from the table. SUMIF appears to only work for one variable.

What function can I use to do this?

Thx
--
herkimer

sum based on 2 criteria within a table
 

That would be SUMPRODUCT()

=SUMPRODUCT(--(A2:A1000="Bob"),--(B2:B1000=12345678),C2:C1000)

Couple of notes:

All three data sets must encompass the same number of rows.

You should not reference, say, A:A. SUMPRODUCT is not a fan of entire column
or row searches.

Of course, I hard coded names, but you'll probably want to enter, dsay, D2
instead of "Bob" and E2 instead of the account #.

"herkimer" wrote:

I am trying to do a SUMIF on a table, but I have multiple criteria in 2
columns.

The table is such that the name repeats several times, as there is another
column with account numbers in it, which in most cases also has multiple
lines. The last column holds dollar amounts.

I want to sum the dollar amounts for a specified name/account number
combination from the table. SUMIF appears to only work for one variable.

What function can I use to do this?

Thx
--
herkimer

sum based on 2 criteria within a table
 

=SUMPRODUCT(--(A1:A100="criiteria 1"),--(B1:B100="criteria 2"),C1:C100)



"herkimer" wrote:

I am trying to do a SUMIF on a table, but I have multiple criteria in 2
columns.

The table is such that the name repeats several times, as there is another
column with account numbers in it, which in most cases also has multiple
lines. The last column holds dollar amounts.

I want to sum the dollar amounts for a specified name/account number
combination from the table. SUMIF appears to only work for one variable.

What function can I use to do this?

Thx
--
herkimer

sum based on 2 criteria within a table
 

Use sumproduct

=SUMPRODUCT((A1:A12="Name")*(B1:B12=123456)*(C1:C1 2))

Mike

"herkimer" wrote:

I am trying to do a SUMIF on a table, but I have multiple criteria in 2
columns.

The table is such that the name repeats several times, as there is another
column with account numbers in it, which in most cases also has multiple
lines. The last column holds dollar amounts.

I want to sum the dollar amounts for a specified name/account number
combination from the table. SUMIF appears to only work for one variable.

What function can I use to do this?

Thx
--
herkimer

sum based on 2 criteria within a table
 

Hi,

If you are using 2007 you can use SUMIFS

=SUMIFS(C1:C6,A1:A6,H1,B1:B6,I1)

In this example C1:C6 is the sum range, A1:A6 is criteria range 1 and H1 is
the first condition, B1:B6 is the second criteria range and I1 contains the
second condtions.

Regardless of which formula you use it is always a good idea to use
references in them rather than hard coding them if at all possible.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"herkimer" wrote:

I am trying to do a SUMIF on a table, but I have multiple criteria in 2
columns.

The table is such that the name repeats several times, as there is another
column with account numbers in it, which in most cases also has multiple
lines. The last column holds dollar amounts.

I want to sum the dollar amounts for a specified name/account number
combination from the table. SUMIF appears to only work for one variable.

What function can I use to do this?

Thx
--
herkimer

sum based on 2 criteria within a table
 

Thank you very much, this is the first time I have used these discussion
groups...and I am overwhelmed and grateful. Works perfect.

Have a great day!


--
herkimer


"Sean Timmons" wrote:

That would be SUMPRODUCT()

=SUMPRODUCT(--(A2:A1000="Bob"),--(B2:B1000=12345678),C2:C1000)

Couple of notes:

All three data sets must encompass the same number of rows.

You should not reference, say, A:A. SUMPRODUCT is not a fan of entire column
or row searches.

Of course, I hard coded names, but you'll probably want to enter, dsay, D2
instead of "Bob" and E2 instead of the account #.

"herkimer" wrote:

I am trying to do a SUMIF on a table, but I have multiple criteria in 2
columns.

The table is such that the name repeats several times, as there is another
column with account numbers in it, which in most cases also has multiple
lines. The last column holds dollar amounts.

I want to sum the dollar amounts for a specified name/account number
combination from the table. SUMIF appears to only work for one variable.

What function can I use to do this?

Thx
--
herkimer

sum based on 2 criteria within a table
 

Thank you very much, this is the first time I have used these discussion
groups...and I am overwhelmed and grateful. Works perfect.

Have a great day!

--
herkimer


"Teethless mama" wrote:

=SUMPRODUCT(--(A1:A100="criiteria 1"),--(B1:B100="criteria 2"),C1:C100)



"herkimer" wrote:

I am trying to do a SUMIF on a table, but I have multiple criteria in 2
columns.

The table is such that the name repeats several times, as there is another
column with account numbers in it, which in most cases also has multiple
lines. The last column holds dollar amounts.

I want to sum the dollar amounts for a specified name/account number
combination from the table. SUMIF appears to only work for one variable.

What function can I use to do this?

Thx
--
herkimer

sum based on 2 criteria within a table
 

Hi Mike H

This didn't do what I needed it to do; see the other responses to this
question...I don't understand the syntax with the dashes inside the brackets,
but it seems to work.

I checked the Help again after getting this guidance, I would never have
gotten this syntax or anything close from the formula help within Excel. In
fact the description of the function would not have made me even look at that
one.

Thanks again for your input
Have a great day
--
herkimer


"Mike H" wrote:

Use sumproduct

=SUMPRODUCT((A1:A12="Name")*(B1:B12=123456)*(C1:C1 2))

Mike

"herkimer" wrote:

I am trying to do a SUMIF on a table, but I have multiple criteria in 2
columns.

The table is such that the name repeats several times, as there is another
column with account numbers in it, which in most cases also has multiple
lines. The last column holds dollar amounts.

I want to sum the dollar amounts for a specified name/account number
combination from the table. SUMIF appears to only work for one variable.

What function can I use to do this?

Thx
--
herkimer

sum based on 2 criteria within a table
 

Shane,

Thank you; this is very well explained. When I get Office 2007 I will keep
this in mind.

Have a great day!
--
herkimer


"ShaneDevenshire" wrote:

Hi,

If you are using 2007 you can use SUMIFS

=SUMIFS(C1:C6,A1:A6,H1,B1:B6,I1)

In this example C1:C6 is the sum range, A1:A6 is criteria range 1 and H1 is
the first condition, B1:B6 is the second criteria range and I1 contains the
second condtions.

Regardless of which formula you use it is always a good idea to use
references in them rather than hard coding them if at all possible.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"herkimer" wrote:

I am trying to do a SUMIF on a table, but I have multiple criteria in 2
columns.

The table is such that the name repeats several times, as there is another
column with account numbers in it, which in most cases also has multiple
lines. The last column holds dollar amounts.

I want to sum the dollar amounts for a specified name/account number
combination from the table. SUMIF appears to only work for one variable.

What function can I use to do this?

Thx
--
herkimer