Summing multiple rows if criteria

[bsl]

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(D45),
0) but this only added two rows, not the 3 rows together.

Many thanks!

 

[John C]

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]

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.

 

[bsl]

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!

 

[bsl]

oops- duh! THAT WORKED!

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]

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).