SOLVED

Counting unique entries

Deleted
Not applicable

I am using the formula =SUMPRODUCT(($G$2:$G$200<>"")/COUNTIFS($D$2:$D$200,$D$2:$D$200&"")) to compile a list of unique entries and this is working well. However, I now want to refine my selection to count unique entries occurring between 1/1/2018 and 31/12/2018 but am struggling to create a working formula. Any suggestions would be most welcome.

 

Thanks in advance

Jim

11 Replies

@Deleted,

You didn't say where the dates were located, so I assumed column E.

 

Try a formula like:

=SUMPRODUCT(IFERROR(($G$2:$G$200<>"")*($E$2:$E$200>=--"1/1/18")*($E$2:$E$200<=--"12/31/18")/
COUNTIFS($D$2:$D$200,$D$2:$D$200&"",$E$2:$E$200,">=1/1/18", $E$2:$E$200,"<=12/31/18"),0))

While you can get away with ">=1/1/18" in the COUNTIFS, you need to convert the date to a number in the SUMPRODUCT. I did so by prefixing it with two minus signs in a row.

Hi Brad …@Brad_Yundt 

 

Many thanks for your reply which I have been experimenting with this morning but, unfortunately, the formula keeps returning the value 0 (Zero) - which is incorrect. If you have any further suggestions I'd be most grateful.

FYI - the dates are all in column G.

 

Jim

@Deleted 

 

Jim, Brad is from United States where months are in the front of days. You may convert dates into your locale format, or, to be not dependent on locale, it is better to use DATE() instead of date constants, like

=SUMPRODUCT(IFERROR(($G$2:$G$200>=DATE(2018,1,1))*($G$2:$G$200<=DATE(2018,12,31))/
COUNTIFS($D$2:$D$200,$D$2:$D$200&"",$G$2:$G$200,">="&DATE(2018,1,1), $G$2:$G$200,"<="&DATE(2018,12,31)),0))

 

@Sergei Baklan 

 

Many thanks Sergei: that worked a treat.

 

Jim

Jim, you are welcome

@Deleted 

Hi Sergei ...

I think I got ahead of myself on Sunday in my enthusiasm for the solution you were providing.

 

When I input your formula to my worksheet, it returned a value of 1 (one) which at the time I assumed was correct. I've now noticed, however, that the actual value being returned is 0.500 and when I highlight the formula and press F9, it shows a value of 8 - which is correct after I did a manual count.

 

Is there anyway the formula can be tweaked? Would much appreciate your input please as I also want to apply the formula to other years.

 

Regards

Jim

best response
Solution

@Deleted 

Hi Jim,

Use any cells as parameters to define your dates range, as A1 and A2 here

image.png

And enter the formula as array one (i.e. Ctrl+Shift+Enter instead of Enter).

@Sergei Baklan 

 

Sergei … now we're cooking. Actually it was my stupidity for forgetting to add the array braces.

 

So again, my warmest thanks for your help and patience.

 

Jim

@Deleted 

Jim, and I forgot to mention. I'm now on the version with Dynamic Arrays which actually doesn't require array formulas.

Hi Sergei,

I need help on similar issue. I need to get the count of unique values in Customer Number column if Month = March and Sales Category = "Decrease"

Month Customer# Sales Category

Thanks in advance for any help.

@ginger2016 

https://1drv.ms/x/s!Aml9MeEtEo3OhxbBPw-mWIc34YFS?e=jhtScc 

 

The above file on my OneDrive shows several different formulas. Two require dynamic arrays for the UNIQUE and FILTER functions. Two do not.

 

I put month as text in column A, customer id in column B, Increase or Decrease in column C, and dates in column F. Cell F1 holds the text "March" and is used to build a date/time serial number.

 

=COUNTA(UNIQUE(FILTER(B2:B23,(A2:A23=F1)*(C2:C23="Decrease"))))
 
=COUNTA(UNIQUE(FILTER(B2:B23,(MONTH(D2:D23)=3)*(C2:C23="Decrease"))))
 
=SUMPRODUCT(IFERROR((A2:A23=F1)*(C2:C23="Decrease")/COUNTIFS(B2:B23,B2:B23,A2:A23,F1,C2:C23,"Decrease"),0))
 
=SUMPRODUCT(IFERROR((MONTH(D2:D23)=3)*(C2:C23="Decrease")/COUNTIFS(B2:B23,B2:B23,D2:D23,">=" & DATEVALUE(F1 & " 1, 2020"),D2:D23,"<=" & EOMONTH(DATEVALUE(F1 & " 1, 2020"),0),C2:C23,"Decrease"),0))

 

1 best response

Accepted Solutions
best response
Solution

@Deleted 

Hi Jim,

Use any cells as parameters to define your dates range, as A1 and A2 here

image.png

And enter the formula as array one (i.e. Ctrl+Shift+Enter instead of Enter).

View solution in original post