SUMIFS and Ignoring Duplicates?

[Maconi]

I have a spreadsheet where I need to sum a column based on if a cell matches on two different worksheets, while being in one of 3 specific columns on the other worksheet, while being adjacent to a cell containing a specific letter and only counting each letter once.

I'm able to accomplish everything but the last criteria by summing 3 SUMIFS together. Is it possible to also do the last part inside the SUMIFS? I've tried tossing a FREQUENCY formula in there to no avail. I can resort to a helper column as well if needed but I was curious as to if there was a simple formula solution?

Image of example:

 

[Aladin Akyurek]

Sorry, I should have explained it better. The only thing I'm actually counting (summing actually) is Quantity. Everything else is just criteria to obtain the SUM of Quantity that I want.

On the first worksheetsheet I have 2 columns. One with all the Z's listed (lets say Z01 through Z99) and another with the their Total Quantity. One the second worksheet I have the table I've been posting. It contains a Key (which can have duplicates), 3 different Types corresponding to the Codes, the Quantity for that Key, and the Codes involved with that Key.

What I want is to SUM Quantity for each Z, but only once per Key/Code combination. For instance, when Z10 is in the TypeA column, if there' also an "A" Code in that row I want to SUM that quantity. That in itself can be handled by a SUMIF. The problem is I only want each Code counted once per Key. So if Key 55555 is listed 3 times, and Z10 appears in the same row all 3 times, and the "A" Code appears in the same row all 3 times, I still only want to SUM the Quantity once for Z10 for that Key.

Does that make more sense? Sorry I didn't explain it as in-depth before. [...]

You want the sum of Quantity for the distinct combinations of Z10 (a TypeA value) and cooresponding Key values and Code = A, right? Please try to specify in a concise manner...

 

[Maconi]

You want the sum of Quantity for the distinct combinations of Z10 (a TypeA value) and cooresponding Key values and Code = A, right? Please try to specify in a concise manner...

Correct... I think lol. For instance, I want to look for Z10 in column B, and SUM the Quantity in column E if "A" is found in column F, but only once per unique Key in column A.

I combined both yours and cerfani's formulas together and it appears to be working as expected. This one returns how many times it finds "A" in column F.

=SUM(IFERROR(IF(FREQUENCY(IF(ISNUMBER(SEARCH("A",$F$2:$F$7)),$A$2:$A$7),$A$2:$A$7),1)*(B2:B7="Z10"),0))
(CTRL+SHIFT+ENTER)

This one SUMS the Quantity like I want, I think. I'll have to test more lol. Thanks for all your help so far!

=SUM(IF(IFERROR(IF(FREQUENCY(IF(ISNUMBER(SEARCH("A",$F$2:$F$7)),$A$2:$A$7),$A$2:$A$7),1)*(B2:B7="Z10"),0),E2:E7,0))
(CTRL+SHIFT+ENTER)

 

[cerfani]

can you use a vba function? it is easy if you can build all the arrays yourself

 

[Aladin Akyurek]

Correct... I think lol. For instance, I want to look for Z10 in column B, and SUM the Quantity in column E if "A" is found in column F, but only once per unique Key in column A.

[...]

H1: A
H2: Z10

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$7),IF($B$2:$B$7=$H2,
    IF(ISNUMBER(SEARCH(H$1&"/",$F$2:$F$7&"/")),MATCH($A$2:$A$7,$A$2:$A$7,0)))),
    ROW($A$2:$A$7)-ROW($A$2)+1),$E$2:$E$7))

This yields a sum on Quantity.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$7),IF($B$2:$B$7=$H2,
    IF(ISNUMBER(SEARCH(H$1&"/",$F$2:$F$7&"/")),MATCH($A$2:$A$7,$A$2:$A$7,0)))),
    ROW($A$2:$A$7)-ROW($A$2)+1),1))

This yields a distinct count.