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:

 

[Maconi]

The main problem is that the result is incorrect. As you can see from the table I posted above, using your formula gives a result of 1. It should give a result of 2 since it should count 1 A from the 3 duplicates, and another A from the entry just above them. I've tried manipulating the formula a few different ways but I can't seem to figure out the issue.

Well I guess I only pasted the formulas. Here are the values.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Key[/TD]
[TD]TypeA[/TD]
[TD]TypeB[/TD]
[TD]TypeC[/TD]
[TD]Quantity[/TD]
[TD]Code[/TD]
[TD][/TD]
[TD][/TD]
[TD]Z10 Type A Code A[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

55553​

[/TD]
[TD]Z15[/TD]
[TD]Z17[/TD]
[TD]Z19[/TD]
[TD]

1.000​

[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

55554​

[/TD]
[TD]Z10[/TD]
[TD]Z12[/TD]
[TD]Z14[/TD]
[TD]

1.000​

[/TD]
[TD]A[/TD]
[TD]+1 A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

55555​

[/TD]
[TD]Z10[/TD]
[TD]Z12[/TD]
[TD]Z14[/TD]
[TD]

1.000​

[/TD]
[TD]A/B[/TD]
[TD]+1 A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

55555​

[/TD]
[TD]Z10[/TD]
[TD]Z12[/TD]
[TD]Z14[/TD]
[TD]

1.000​

[/TD]
[TD]A[/TD]
[TD]Duplicate A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

55555​

[/TD]
[TD]Z10[/TD]
[TD]Z12[/TD]
[TD]Z14[/TD]
[TD]

1.000​

[/TD]
[TD]A/C[/TD]
[TD]Duplicate A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

55556​

[/TD]
[TD]Z05[/TD]
[TD]Z07[/TD]
[TD]Z09[/TD]
[TD]

1.000​

[/TD]
[TD]C[/TD]
[TD]Only want each Code to count once per Key. So Code "A" for Key "55555" should return 2 in this example.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cerfani's Formula[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]My Attempt[/TD]
[TD]

1​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Looks like...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

1​

[/td][td] Key[/td][td] TypeA[/td][td] TypeB[/td][td] TypeC[/td][td] Quantity[/td][td] Code[/td][td] [/td][td] A[/td][td][/td][/tr]


[tr][td]

2​

[/td][td]

55553​

[/td][td] Z15[/td][td] Z17[/td][td] Z19[/td][td]

1​

[/td][td] B[/td][td] [/td][td]

​

[/td][td][/td][/tr]


[tr][td]

3​

[/td][td]

55554​

[/td][td] Z10[/td][td] Z12[/td][td] Z14[/td][td]

1​

[/td][td] A[/td][td][/td][td] 2[/td][td] [/td][/tr]


[tr][td]

4​

[/td][td]

55555​

[/td][td] Z10[/td][td] Z12[/td][td] Z14[/td][td]

1​

[/td][td] A/B[/td][td][/td][td] [/td][td] [/td][/tr]


[tr][td]

5​

[/td][td]

55555​

[/td][td] Z10[/td][td] Z12[/td][td] Z14[/td][td]

1​

[/td][td] A[/td][td][/td][td] [/td][td] [/td][/tr]


[tr][td]

6​

[/td][td]

55555​

[/td][td] Z10[/td][td] Z12[/td][td] Z14[/td][td]

1​

[/td][td] A/C[/td][td][/td][td] [/td][td] [/td][/tr]


[tr][td]

7​

[/td][td]

55556​

[/td][td] Z05[/td][td] Z07[/td][td] Z09[/td][td]

1​

[/td][td] C[/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]
<strike></strike>
H3, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(H$1&"/",$F$2:$F$7&"/")),$A$2:$A$7),$A$2:$A$7),1))<strike></strike>

Is this what you are after?

 

[cerfani]

=SUM(IFERROR((FREQUENCY(MATCH(A2:A7,A2:A7,0),MATCH(A2:A7,A2:A7,0))>0)*(IFERROR(SEARCH(I2,F2:F7),0)>0),0))

this should work

 

[Maconi]

Looks like...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD] Key[/TD]
[TD] TypeA[/TD]
[TD] TypeB[/TD]
[TD] TypeC[/TD]
[TD] Quantity[/TD]
[TD] Code[/TD]
[TD][/TD]
[TD] A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

55553​

[/TD]
[TD] Z15[/TD]
[TD] Z17[/TD]
[TD] Z19[/TD]
[TD]

1​

[/TD]
[TD] B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

55554​

[/TD]
[TD] Z10[/TD]
[TD] Z12[/TD]
[TD] Z14[/TD]
[TD]

1​

[/TD]
[TD] A[/TD]
[TD][/TD]
[TD] 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

55555​

[/TD]
[TD] Z10[/TD]
[TD] Z12[/TD]
[TD] Z14[/TD]
[TD]

1​

[/TD]
[TD] A/B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

55555​

[/TD]
[TD] Z10[/TD]
[TD] Z12[/TD]
[TD] Z14[/TD]
[TD]

1​

[/TD]
[TD] A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

55555​

[/TD]
[TD] Z10[/TD]
[TD] Z12[/TD]
[TD] Z14[/TD]
[TD]

1​

[/TD]
[TD] A/C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

55556​

[/TD]
[TD] Z05[/TD]
[TD] Z07[/TD]
[TD] Z09[/TD]
[TD]

1​

[/TD]
[TD] C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

<strike></strike>
H3, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(H$1&"/",$F$2:$F$7&"/")),$A$2:$A$7),$A$2:$A$7),1))<strike></strike>

Is this what you are after?

Almost. This is the final goal:

Lookup the total quantity (ignoring duplicates, based on the Key) for each Z based on the Type column they're in. So lets say if Z10 is in the TypeA column, then I want to count the total (ignoring duplicates, based on the Key) "A" Codes.

So in the example, Z10 would have 2 unique "A" codes, 1 from Key 55554 and 1 from Key 55555, because it's in the Type A column.

Hopefully that makes sense.

 

[Aladin Akyurek]

Almost. This is the final goal:

Lookup the total quantity (ignoring duplicates, based on the Key) for each Z based on the Type column they're in. So lets say if Z10 is in the TypeA column, then I want to count the total (ignoring duplicates, based on the Key) "A" Codes.

So in the example, Z10 would have 2 unique "A" codes, 1 from Key 55554 and 1 from Key 55555, because it's in the Type A column.

Hopefully that makes sense.

I hope you are not trying to confuse...

You want a distinct count of keys, right?

 

[cerfani]

i didnt add the check for TypeA but easy enough to add

=SUM(IFERROR((FREQUENCY(MATCH(A2:A7,A2:A7,0),MATCH(A2:A7,A2:A7,0))>0)*(IFERROR(SEARCH(I2,F2:F7),0)>0)*(B2:B7=I1),0))

 

[Maconi]

I hope you are not trying to confuse...

You want a distinct count of keys, right?

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.

i didnt add the check for TypeA but easy enough to add

=SUM(IFERROR((FREQUENCY(MATCH(A2:A7,A2:A7,0),MATCH(A2:A7,A2:A7,0))>0)*(IFERROR(SEARCH(I2,F2:F7),0)>0)*(B2:B7=I1),0))

Much closer, thanks for all your help cerfani. I think something is still possibly off thought. The formula doesn't work for "C" even thought it should pick up the C on line 6 right?

EDIT: It appears to be the FREQUENCY formula. Here's a quick peek (you can see line 6 returns a different boolean than the other duplicates).

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][td]

Q​

[/td][/tr][tr][td]

1​

[/td][td]Key[/td][td]TypeA[/td][td]TypeB[/td][td]TypeC[/td][td]Quantity[/td][td]Code[/td][td][/td][td][/td][td]Z10 Type A Code A[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

55553​

[/td][td]Z15[/td][td]Z17[/td][td]Z19[/td][td]

1.000​

[/td][td]B[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Yes[/td][/tr]
[tr][td]

3​

[/td][td]

55554​

[/td][td]Z10[/td][td]Z12[/td][td]Z14[/td][td]

1.000​

[/td][td]A[/td][td]+1 A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Yes[/td][/tr]
[tr][td]

4​

[/td][td]

55555​

[/td][td]Z10[/td][td]Z12[/td][td]Z14[/td][td]

1.000​

[/td][td]A/B[/td][td]+1 A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Yes[/td][/tr]
[tr][td]

5​

[/td][td]

55555​

[/td][td]Z10[/td][td]Z12[/td][td]Z14[/td][td]

1.000​

[/td][td]A[/td][td]Duplicate A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]No[/td][/tr]
[tr][td]

6​

[/td][td]

55555​

[/td][td]Z10[/td][td]Z12[/td][td]Z14[/td][td]

1.000​

[/td][td]A/C[/td][td]Duplicate A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]No[/td][/tr]
[tr][td]

7​

[/td][td]

55556​

[/td][td]Z05[/td][td]Z07[/td][td]Z09[/td][td]

1.000​

[/td][td]C[/td][td]Only want each Code to count once per Key. So Code "A" for Key "55555" should return 2 in this example.[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Yes[/td][/tr]
[/table]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][td]

Q​

[/td][/tr][tr][td]

1​

[/td][td]Key[/td][td]TypeA[/td][td]TypeB[/td][td]TypeC[/td][td]Quantity[/td][td]Code[/td][td][/td][td][/td][td]Z10 Type A Code A[/td][td]

=SUM(SUMIFS(E:E,B:B,Sheet2!A2,F:F,"*A*"),SUMIFS(E:E,C:C,Sheet2!A2,F:F,"*B*"),SUMIFS(E:E,D:D,Sheet2!A2,F:F,"*C*"))​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

55553​

[/td][td]Z15[/td][td]Z17[/td][td]Z19[/td][td]

1.000​

[/td][td]B[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]{=IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0)),"Yes","No")}[/td][/tr]
[tr][td]

3​

[/td][td]

55554​

[/td][td]Z10[/td][td]Z12[/td][td]Z14[/td][td]

1.000​

[/td][td]A[/td][td]+1 A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]{=IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0)),"Yes","No")}[/td][/tr]
[tr][td]

4​

[/td][td]

55555​

[/td][td]Z10[/td][td]Z12[/td][td]Z14[/td][td]

1.000​

[/td][td]A/B[/td][td]+1 A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]{=IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0)),"Yes","No")}[/td][/tr]
[tr][td]

5​

[/td][td]

55555​

[/td][td]Z10[/td][td]Z12[/td][td]Z14[/td][td]

1.000​

[/td][td]A[/td][td]Duplicate A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]{=IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0)),"Yes","No")}[/td][/tr]
[tr][td]

6​

[/td][td]

55555​

[/td][td]Z10[/td][td]Z12[/td][td]Z14[/td][td]

1.000​

[/td][td]A/C[/td][td]Duplicate A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]{=IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0)),"Yes","No")}[/td][/tr]
[tr][td]

7​

[/td][td]

55556​

[/td][td]Z05[/td][td]Z07[/td][td]Z09[/td][td]

1.000​

[/td][td]C[/td][td]Only want each Code to count once per Key. So Code "A" for Key "55555" should return 2 in this example.[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]{=IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0)),"Yes","No")}[/td][/tr]
[/table]

 

[Maconi]

Meant to say line 4, can't edit now though.

My point was, it's completely ignoring line 5 and 6. If a Code appears in a duplicated line, it still needs to be counted at least once. (so Code A, B, and C should all have a total of 1 for Key 55555)

 

[cerfani]

i see what you are saying, that frequency part isnt working properly. I will see if I can edit it.

 

[cerfani]

i was playing around with it and these kind of formulas that need to look and consider other values in the array are tricky due to the way array formulas are handled in excel.

Excel formula arrays are like for loops in vba but with strict rules... so doing this in vba is easier BUT i am sure there is a way to do via formula... You just need to get clever.

If you debug my formula by evaluating... you can see why it breaks... the trick is to get the right values in the same positions and in your case it would be easier in vba.