SOLVED

Deleted
Not applicable

# Counting unique entries

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.

Jim

11 Replies

# Re: Counting unique entries

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

# Re: Counting unique entries

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

# Re: Counting unique entries

@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))```

# Re: Counting unique entries

Many thanks Sergei: that worked a treat.

Jim

# Re: Counting unique entries

Jim, you are welcome

# Re: Counting unique entries (again!!!)

@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

# Re: Counting unique entries (again!!!)

@Deleted

Hi Jim,

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

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

# Re: Counting unique entries (again!!!)

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

# Re: Counting unique entries (again!!!)

@Deleted

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

# Re: Counting unique entries (again!!!)

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.

# Re: Counting unique entries (again!!!)

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))
``````