| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum based on column and row criteria | Excel Discussion (Misc queries) | |||
| Add a row based on other criteria | Excel Worksheet Functions | |||
| summing values in a data table based on criteria in another column | Excel Worksheet Functions | |||
| Need help looking up value based on criteria | Excel Worksheet Functions | |||
| calculate the sum based on two different criteria | Excel Discussion (Misc queries) | |||
Linear Mode
