Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing multiple rows if criteria
Here's my example:
A B Qty Total d y 2 d z 3 e x 4 e x 10 e x 11 25 e y 5 f x 1 I want to add the numbers in the Quantity Field, but only where you have duplicates. For example, in this case, QTY 4, 10, 15 would be added in a new column "Total" as Column B's "x" was the same for every Column A's e. No other additions would take place. The database has 5,000 rows and sometimes there are two duplicates, but other times there are 10 duplicates, so I haven't been able to do a sum product. I tried =IF(B4=B5,SUMPRODUCT(D4:D5), 0) but this only added two rows, not the 3 rows together. Many thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing multiple rows if criteria
I have your A range in column A, your B range in column B, and your Qty in
column C, with Total in column D, modify as necessary: in D2, type the following, then fill down as far as needed. =IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=C2,"",IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2),(C$2:C$8)),SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2),(C$2:C$8)),"")) Hope this helps. -- John C "bsl" wrote: Here's my example: A B Qty Total d y 2 d z 3 e x 4 e x 10 e x 11 25 e y 5 f x 1 I want to add the numbers in the Quantity Field, but only where you have duplicates. For example, in this case, QTY 4, 10, 15 would be added in a new column "Total" as Column B's "x" was the same for every Column A's e. No other additions would take place. The database has 5,000 rows and sometimes there are two duplicates, but other times there are 10 duplicates, so I haven't been able to do a sum product. I tried =IF(B4=B5,SUMPRODUCT(D4:D5), 0) but this only added two rows, not the 3 rows together. Many thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing multiple rows if criteria
Obviously, expand the ranges that show A$2:A$8 etc to whatever is the last
row needed, but be sure to leave the portions that might show A$2:A2 as is. -- John C "John C" wrote: I have your A range in column A, your B range in column B, and your Qty in column C, with Total in column D, modify as necessary: in D2, type the following, then fill down as far as needed. =IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=C2,"",IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2),(C$2:C$8)),SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2),(C$2:C$8)),"")) Hope this helps. -- John C "bsl" wrote: Here's my example: A B Qty Total d y 2 d z 3 e x 4 e x 10 e x 11 25 e y 5 f x 1 I want to add the numbers in the Quantity Field, but only where you have duplicates. For example, in this case, QTY 4, 10, 15 would be added in a new column "Total" as Column B's "x" was the same for every Column A's e. No other additions would take place. The database has 5,000 rows and sometimes there are two duplicates, but other times there are 10 duplicates, so I haven't been able to do a sum product. I tried =IF(B4=B5,SUMPRODUCT(D4:D5), 0) but this only added two rows, not the 3 rows together. Many thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing multiple rows if criteria
thank you, but that didn't work. another issue perhaps is that there are
about 100 possibilities in column A, and that many for column B. I need add all instances in QTY where A and B row 1 match A and B row2, 3, 4, etc...If they don't match,no addition. Thanks for your help! "John C" wrote: I have your A range in column A, your B range in column B, and your Qty in column C, with Total in column D, modify as necessary: in D2, type the following, then fill down as far as needed. =IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=C2,"",IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2),(C$2:C$8)),SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2),(C$2:C$8)),"")) Hope this helps. -- John C "bsl" wrote: Here's my example: A B Qty Total d y 2 d z 3 e x 4 e x 10 e x 11 25 e y 5 f x 1 I want to add the numbers in the Quantity Field, but only where you have duplicates. For example, in this case, QTY 4, 10, 15 would be added in a new column "Total" as Column B's "x" was the same for every Column A's e. No other additions would take place. The database has 5,000 rows and sometimes there are two duplicates, but other times there are 10 duplicates, so I haven't been able to do a sum product. I tried =IF(B4=B5,SUMPRODUCT(D4:D5), 0) but this only added two rows, not the 3 rows together. Many thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing multiple rows if criteria
oops- duh! THAT WORKED!
"John C" wrote: Obviously, expand the ranges that show A$2:A$8 etc to whatever is the last row needed, but be sure to leave the portions that might show A$2:A2 as is. -- John C "John C" wrote: I have your A range in column A, your B range in column B, and your Qty in column C, with Total in column D, modify as necessary: in D2, type the following, then fill down as far as needed. =IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=C2,"",IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2),(C$2:C$8)),SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2),(C$2:C$8)),"")) Hope this helps. -- John C "bsl" wrote: Here's my example: A B Qty Total d y 2 d z 3 e x 4 e x 10 e x 11 25 e y 5 f x 1 I want to add the numbers in the Quantity Field, but only where you have duplicates. For example, in this case, QTY 4, 10, 15 would be added in a new column "Total" as Column B's "x" was the same for every Column A's e. No other additions would take place. The database has 5,000 rows and sometimes there are two duplicates, but other times there are 10 duplicates, so I haven't been able to do a sum product. I tried =IF(B4=B5,SUMPRODUCT(D4:D5), 0) but this only added two rows, not the 3 rows together. Many thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing multiple rows if criteria
I assure you, it does work, kid tested, mother approved. The key is some
parts of the formula are 'anchored' from the beginning of your data set to the end of your dataset, and some parts of the formula ONLY check from the beginning of the data set to the row that it is at. Say your data set runs from rows 2 through 1000, then your formula should look like this: =IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=C2,"",IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=SUMPRODUCT(--(A$2:A$1000=A2),--(B$2:B$1000=B2),(C$2:C$1000)),SUMPRODUCT(--(A$2:A$1000=A2),--(B$2:B$1000=B2),(C$2:C$1000)),"")) Copy and paste this formula exactly as is into row 2 of whatever column you want this in. Modify the column letters if needed (see how I assumed your data was before), ensuring that if you need to change column references, you change all of them. Then select this cell, and Edit--Fill--Down all the way to the end of your data set (in this example, row 1000). -- John C "bsl" wrote: thank you, but that didn't work. another issue perhaps is that there are about 100 possibilities in column A, and that many for column B. I need add all instances in QTY where A and B row 1 match A and B row2, 3, 4, etc...If they don't match,no addition. Thanks for your help! "John C" wrote: I have your A range in column A, your B range in column B, and your Qty in column C, with Total in column D, modify as necessary: in D2, type the following, then fill down as far as needed. =IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=C2,"",IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),(C$2:C2))=SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2),(C$2:C$8)),SUMPRODUCT(--(A$2:A$8=A2),--(B$2:B$8=B2),(C$2:C$8)),"")) Hope this helps. -- John C "bsl" wrote: Here's my example: A B Qty Total d y 2 d z 3 e x 4 e x 10 e x 11 25 e y 5 f x 1 I want to add the numbers in the Quantity Field, but only where you have duplicates. For example, in this case, QTY 4, 10, 15 would be added in a new column "Total" as Column B's "x" was the same for every Column A's e. No other additions would take place. The database has 5,000 rows and sometimes there are two duplicates, but other times there are 10 duplicates, so I haven't been able to do a sum product. I tried =IF(B4=B5,SUMPRODUCT(D4:D5), 0) but this only added two rows, not the 3 rows together. Many thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing and multiplying for multiple criteria | Excel Discussion (Misc queries) | |||
Need Help: Summing Multiple Criteria | Excel Worksheet Functions | |||
Summing Fields with Multiple Criteria | Excel Discussion (Misc queries) | |||
Summing Rows with Multiple Criteria | Excel Worksheet Functions | |||
Summing with multiple criteria | Excel Worksheet Functions |