CountIf without Duplicates

[The_Game]

I want to account for the total number of assets on hand, but there seems to be a duplicate problem I can't solve. I've got a total of 45 items under 2 customers, with 7 cells formulating for all items status/location. The current formula I'm using to count the totals is =SUM(M8,P8,P6,O6,M6+COUNTIF(E5:E49,"DL10"))-2. This is pulling from a large table, and since my formula wants to count the AWM and DL10s, its double counting items labeled with both. How can I count only one where AWM takes priority?


All cells contain formulas to count respective cells matching criteria. Table1=T1
[TABLE="width: 500"]
<tbody>[TR]
[TD]A1
[/TD]
[TD]M6
[/TD]
[TD]O6 T1(F5:F49)
[/TD]
[TD]P6
[/TD]
[TD]T1(E5:E49)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total (45)
[/TD]
[TD]INW
[/TD]
[TD]AWM
[/TD]
[TD]AWP
[/TD]
[TD]DL10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]M8 ->
[/TD]
[TD]Customer 1
[/TD]
[TD]Customer 2
[/TD]
[TD]<- P8
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15
[/TD]
[TD]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[crazydragon84]

no offense, but your data table doesn't make any sense to me lol I have no idea what you want so i'm not sure if this will help, but if you just want to count the number of uniques, just copy the entire column to a separate sheet and use the "remove duplicate" function (under Data>Data Tools).

 

[The_Game]

lol, you're fine. I was afraid I'd get a reply as such when the table cells were removed for some reason. but the M6, O6 and P6 at the top are refer to the cells below them and the formula I talked about: =SUM(M8,P8,P6,O6,M6+COUNTIF(E5:E49,"DL10"))-2. I tired updating the post, but it wont take.

 

[The_Game]

lol, you're fine. I was afraid I'd get a reply as such when the table cells were removed for some reason. but the M6, O6 and P6 at the top are refer to the cells below them and the formula I talked about: =SUM(M8,P8,P6,O6,M6+COUNTIF(E5:E49,"DL10"))-2. This formula is pulling from a table with column labels: INW (M6), AWM (O6), AWP (P6), and DL10. The first three are individual cells counting each match from the table, where the last is one I want to incorporate into my total (A1). The table columns AWM is (F5:F49) and DL10 is (E5:E49). But if a single item is labeled AWM and DL10, it'll count this item twice. I'd use data tools, but it doesn't apply to this total formula as its outside of the table.