0

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

pnuts
  • 58,317
  • 11
  • 87
  • 139
Bryan
  • 1
  • 1
  • Can you give us some sample input and output? – Mr. Mascaro Oct 02 '14 at 20:15
  • 2
    http://stackoverflow.com/questions/20532773/how-to-count-unique-values-in-excel-with-two-conditions – John Bustos Oct 02 '14 at 20:18
  • @JohnBustos I couldn't seem to get it from that post.... – Bryan Oct 02 '14 at 23:17
  • @jbarker2160-cant send more of sample as it's confidential data. – Bryan Oct 02 '14 at 23:19
  • 1
    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 Oct 03 '14 at 06:56
  • @XORLX i am new to the site and I canned find out how to attach the sample file on to the message board. any suggestions? – Bryan Oct 03 '14 at 21:05
  • @pnuts here is the sample file https://www.dropbox.com/s/hxbt7hb9l8hf4w6/Sample%20Example.xlsx?dl=0 – Bryan Oct 08 '14 at 15:51
  • @pnuts I'd rather not use distinct count as using power pivot makes the application unstable. Any solutions using regular excel functions or do I need to use power pivot? – Bryan Oct 08 '14 at 19:28
  • @pnuts I did not use a pivot, I am assuming OP means original Pivot. What does OP mean? I just used excel functions to get the table from the data. – Bryan Oct 08 '14 at 20:48
  • Can you add helper columns to your input sheet? – Captain Oct 09 '14 at 11:22
  • @Captain what do you mean by helper columns? – Bryan Oct 10 '14 at 21:22
  • @Bryan - could you add a column on your input sheet with an intermediary formula... or is you input sheet something that you cannot touch? If the input sheet had a column with formula of `=1/COUNTIFS(...your categorization...)` you can then `SUMIFS` on summary sheet to get the equivalent to the distinct count – Captain Oct 11 '14 at 05:40
  • @Captain In a perfect world id like the input sheet to stay the same, but if I needed a helper column to get the job done, I am open to it. Could you give me a little more detail on how I would do this? Thanks for your help! – Bryan Oct 13 '14 at 16:35
  • @Captain Thanks for the help! I made the countifs helper column and then put the following formula on the calculation sheet, but to no avail...I think I am almost there. Any thoughts=+SUMIFS(Data!$BG:$BG,Data!$AO:$AO,"="&$A105,Data!$L:$L,"="&B$104,Data!$P:$P,$B$103) – Bryan Oct 15 '14 at 19:34

1 Answers1

0

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)
Captain
  • 2,148
  • 15
  • 13