Sum of values with unique criteria in different columns

Hi Everyone,

I have question about summing up of values in a column based upon criteria in a different column (I can't use macros or VBA). I've attached a sample spreadsheet, where I need a value calculated in Column H (highlighted in Yellow). Let me explain...

I have different items (Column A) that use different Flavors (Column C) in a certain Quantities (Column B). Some of the Items may be duplicated (in Column A...like SKU-1 & SKU-2) and some Items may not have any Quantity Used (Column B...like SKU-3 in B14 and SKU-222 in B16).

What I want is to sum all of the Flavor Used (by Flavor) in H2 to H8, summing up the Quantities (Column B) of only the unique values of Items (Column A). In other words, I don't want to count SKU-1's Quantities twice (since they appear twice), only once.

Thus, the total of Vanilla used (formula needed for H2) should be 171, not 173 (if B13 is counted, as a duplicate of B2) and the total of Chocolate (formula needed for H3) should be 31, not 40 (if B15 is counted, as a duplicate of B3).

I know (or rather think) that some use of SUMIF or SUMIFS is a good way to go, but I'm a bit stuck (I can get the SUMIF to work, but my version is counting multiple instances of the same SKU). I found a possible idea at http://www.excelforum.com/excel-gene...-criteria.html, but I need it as a Summary table, like I have in my sample spreadsheet.

BTW, I cannot add any new colums to the original spreadsheet (i.e. I can't add info to Column D), but I can do whatever I want in Column J and beyond (the summary table is editable, but the original info is not).

Can somebody help please?

Thank you in advance,

Yury

Hi
Use in H2 and copy down

Formula:

Please Login or Register  to view this content.

Try this and reply if it works or not

to correct formula in post #2

Hi
Use in H2 and copy down
Formula: Select Code copy to clipboard

Formula:

Please Login or Register  to view this content.

Hi Jose,

Thank you for the formula, but it's still "double counting" duplicate instances of the same Item (from Column A). The correct calculation for H2 is "171", but your formula is showing "174" (even the updated formula that you reposted)...this is also the case in H3, which should be "31", but your formula is showing "40".

Any way to not "double count" without using a "helper" column?

Let me know. Thank you,

Yury

Hi Glenn,

Thank you for your response, but it uses a "Helper" column, which I cannot use/add to the original data set. Is there any way to do this without a "Helper" column in the original data?

I can certainly put the "Helper" column in the Summary Sheet (and then hide it), but is there a way not to use a "Helper" column, if possible? If not, I will use this column and hide it in the Summary sheet.

Thank you in advance,

Yury

Hi hitesh102,

Thank you for your response, but (like Glenn) it uses a "Helper" column, which I cannot use/add to the original data set. Is there any way to do this without a "Helper" column in the original data?

I can certainly put the "Helper" column in the Summary Sheet (and then hide it), but is there a way not to use a "Helper" column, if possible? If not, I will use this column and hide it in the Summary sheet.

Thank you in advance,

Yury

Hi AlKey,

Thank you for the great solution! It works perfectly!

Yury

Hi
I suppose not work well.

If you clean B3 the formula must be work because SKU-2 is 9 in B15

The formula I proposed is based on AlKey solution but it is an array formula (must be enter with CTRL+SHIFT+ENTER)

Formula:

Please Login or Register  to view this content.

Hi Jose,

Thank you for your adjustment! I didn't see that oversight. I was so happy that the formula worked better than what I could come up with, that I stopped double checking after the first 2 rows (that were correct) .

Thank you for helping me on this.

All the best,

Yury

Thank you AlKey,

Your formula (with Jose's adjustment) works great! Thank you again for your help .

Yury