• 464K Members
• 8,208 Online
• 561K Conversations
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

9 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

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies