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:

 

[cerfani]

the problem with sumifs is you can only look for equality, inequality or comparison of numbers. You can't do any kind of test outside that. Your codes are strings and require parsing so you are limited to equality/inequality tests.

I guess would need to write an array formula that can perform more complicated boolean tests (like you would use SEARCH instead and look for your code in column F and see if that is the SEARCH result is > 0) on what you need to test first to get it into a testable format.

If you listed only a single code per row, then this would be easy.

 

[cerfani]

=SUM(IF((B:B=Sheet2!A2)*(IFERROR(SEARCH("A",F:F),0)>0),E:E,0))

CTRL+SHIFT+ENTER (array formula)

 

[Aladin Akyurek]

The image you posted cannot be read into Excel and computed with it. Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit (Note that a link to a storage site like dropbox or onedrive etc. does not provide a permanent record.). Mind you a forum without a permanent record is not of much value.

 

[cerfani]

i notice you said you only want to sum once for a specific key... then you would divide the result of my formula by the count of the specific key containing A code and of the same TypeA

=SUM(IF((B:B=Sheet2!A2)*(IFERROR(SEARCH("A",F:F),0)>0)[FONT=Verdana]*(A:A=55555)[/FONT],E:E,0))/[FONT=Verdana]SUM((B:B=Sheet2!A2)*(IFERROR(SEARCH("A",F:F),0)>0)*(A:A="55555"))[/FONT]

CTRL+SHIFT+ENTER (array formula)

EDIT ALL RANGES TO USE SPECIFIC ROWS... DON'T LOOK THROUGH ENTIRE COLUMNS UNLESS ABSOLUTELY NECESSARY

edit: needed to add column A test and just reference the specific key wherever it is

 

[cerfani]

forgot to remove quotes from "55555" fyi

use this... and maybe you can write something more efficient when you understand it...

=IFERROR(SUM(IF((B2:B10=Sheet2!A2)*(IFERROR(SEARCH("A",F2:F10),0)>0)*(A2:A10=55555),E2:E10,0))/SUM((B2:B10=Sheet2!A2)*(IFERROR(SEARCH("A",F2:F10),0)>0)*(A2:A10=55555)),0)

again Ctrl+Shift+Enter and reference your key appropriately... i just hardcoded 55555 and used rows 2:10

 

[Maconi]

Alright, so I ran into a few issues with the formula.

First is that the "B2:B10=Sheet2!A2" section doesn't work in my case as one cell is Text and the other is General. My two options is switching that to a SEARCH (which I tested and works fine) or setting my macro (which this formula is going into) to just reformat the column before running the formula. Not much of an issue but just mentioning it.

Second is that the "A2:A10=55555" formula doesn't work. It needs to be dynamic and search for ANY duplicate values, not just those matching 55555.

Speaking of which, I see you mention not to reference entire columns (probably due to performance reasons). The problem is the worksheet this formula will be applied to is dynamic (raw data is pulled from another source and pasted into this worksheet daily). So column reference are needed.

Keep in mind that if this can be better accomplished (easier or better performance) by using helper columns or VBA code I'm fine with that. I just wasn't sure if there was a simple tweak to the formula that could accomplish what I was looking for.

[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]

=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]
[/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]
[/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]

{=IFERROR(SUM(IF((B:B=Sheet2!A2)*(IFERROR(SEARCH("A",F:F),0)>0)*(A:A=55555),E:E,0))/SUM((B:B=Sheet2!A2)*(IFERROR(SEARCH("A",F:F),0)>0)*(A:A=55555)),0)}​

[/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]

{=IFERROR(SUM(IF((IFERROR(SEARCH(Sheet2!A2,B:B),0)>0)*(IFERROR(SEARCH("A",F:F),0)>0)*(A:A=A:A),E:E,0))/SUM((IFERROR(SEARCH(Sheet2!A2,B:B),0)>0)*(IFERROR(SEARCH("A",F:F),0)>0)*(A:A=A:A)),0)}​

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

 

[cerfani]

the division is there to eliminate duplicate values being added... here is a short translation of the formula I wrote...

=SumOFColumnEMatchingCriteria/CountofRecordsMatchingCriteria

so the formula eliminates duplicate values, the same way you calculate an average

and to make the 55555 dynamic, you just reference the cell that has the value... you never said where it was so i hardcoded it. You can edit that.

 

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

 

[cerfani]

I must have understood your explanation wrong then... I will read it again.

edit... OK i know what you mean now. I believe instead of summing all results you can edit that and you need to get a count of ids... maybe i can edit it. This kind of stuff when you need to have lists in memory i like to just use vba since it is more intuitive for me.