COUNTIF Unique values

0
votes

I used this formula to find SKU counts in certain categories given multiple criteria: 

=+COUNTIFS(Data!$AG:$AG, ">"&0,Data!$AO:$AO, "="&$A115,Data!$L:$L, "="&D$104,Data!$P:$P,$B$103)

I am able to find the SKU counts but it gives me the total SKU count, I believe because it now counts any SKU with inventory greater than 0 with my multiple criteria.

How can I change the formula to get the unique SKU count per division? 

Column AG: Inventory
Column AO: Division Name
Column  L: Month
Column  P: Year
Column  T: SKU Code(written out) - what I need to find unique values of.

Example here: dropbox.com/s/hxbt7hb9l8hf4w6/Sample%20Example.xlsx?dl=0

1
excelexcel-formulauniquecountif
Can you give us some sample input and output? – Mr. Mascaro
stackoverflow.com/questions/20532773/… – John Bustos
@JohnBustos I couldn't seem to get it from that post.... – Bryan
@jbarker2160-cant send more of sample as it's confidential data. – Bryan
But can't you at least create a small sample of mocked-up dummy data which at least illustrates what you are trying to do? – XOR LX

1 Answers

0
votes

If you created a helper column on the input sheet with the formula (e.g. in BG2):

=1/COUNTIFS(T:T,T2,AG:AG,">0",AO:AO,AO2,L:L,L2,P:P,P2)

This means that if you had, for example, 5 times the SKU in one Division/Month/Year, you would get 1/5. Then, in the calculation sheet, replace your COUNTIFS with a SUMIFS (summing your new helper column, using same conditions). This will mean that your 5 duplicates (with 1/5 in the helper) get summed to 1 - i.e. 1 unique SKU

EDIT

Because there are some where there is no positive inventory, the count gives you 0 and you get a #DIV/0! error... so we can fix that with: 

=IFERROR(1/COUNTIFS(T:T,T2,AG:AG,">0",AO:AO,AO2,L:L,L2,P:P,P2),0)

Then, in the Output-EOM Unique SKU Count sheet, in cell e.g. B6, use: 

=SUMIFS(input!$BG:$BG,input!$AO:$AO,$A6,input!$L:$L,B$5,input!$P:$P,$B$4)