# Counting unique values with multiple criteria. Not working when including Date in Formula

1. ## Counting unique values with multiple criteria. Not working when including Date in Formula

With below formula I am counting unique values. Everything was working well until I added the dates. Any suggestions to solve this?

Formula:
`Please Login or Register  to view this content.`

Thanks
Thomas

2. ## Re: Counting unique values with FREQUENCY, ISNUMBER, SEARCH, and MATCH

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

3. ## Re: Counting unique values with FREQUENCY, ISNUMBER, SEARCH, and MATCH

Please add a sample sheet, too (yellow banner, top of page). It makes it easier if we can SEE what we're dealing with...

4. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

Hi both, thanks for letting me know
Sample file attached and title is now changed.
Hope everything is OK now.

5. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

This is HUGELY slow. Do you REALLY Have 1,000,000 rows????

6. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

Since you have O365, you can use a combination of dynamic arrays:

=COUNTA(UNIQUE(FILTER(F2:F100,(D2:D100>=DATE(2020,1,1))*(D2:D100<=DATE(2020,12,31))*(ISNUMBER(SEARCH("Visit",I2:I100)))*(J2:J100="Ole Olsen")*(N2:N100="Installer/Contractor"),"")))

I am completely confused about what you THINK this bit is doing:

(ISNUMBER(SEARCH("|"&visits!I:I&"|";"|Visit|Online Meeting|Webinar|Join Visit|")))

7. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

Hi Glenn,
Sorry for bringing misinformation. My version is 2016 (is corrected now).

About the bit: This part of the formula is counting "Visit", "Online Meeting", "Webinar" OR "Join Visit".
In the sample file all values are "Visit". But it could easily be one of the other.

Hope it makes sense.

8. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

OK. Back to the drawing board. BUT NEVER use whole column references in array formulae. That formula takes SECONDS to calculate. How many rows (maximum) do you REALLY have?

9. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

Oh yeah. You are very right. I only have 10000 rows.

10. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

OK. I have set it here for 10000 rows. It is still slow... but not as slow... it does have more work to do, though...

=SUM(INDEX((D2:D10000>=DATE(2020,1,1))*(D2:D10000<=DATE(2020,12,31))*(F2:F10000<>"")*(J2:J10000="Ole Olsen")*(ISNUMBER(SEARCH({"ĤVisitĤ","ĤOnline MeetingĤ","ĤWebinarĤ","ĤJoin VisitĤ"},"Ĥ"&I2:I10000&"Ĥ")))/COUNTIFS(I2:I10000,I2:I10000&"",J2:J10000,J2:J10000&"",N2:N10000,N2:N10000&"",F2:F10000,F2:F10000&""),0))

Glenn

11. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

Check to see if it has the array formulae {} round it, on receipt. I am not sure, but the addition of the pipe round column I (to prevent double counting of Visit and Join Visit) may mean that you will have to enter it as an array in your version of Excel.

12. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

Looks very good. I just tried on the sample file and the result is 1.
I have replaced , with ; and tried with and without {}.
Result should be 9.

Also noted the part of formula is missing: N2:N1000="Installer/Contractor"

Thomas

13. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

What did you see when you opened MY file, with the formula in place (yellow cell)?

14. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

Hold on. I just checked your sample file and you have used "\". I have added ="Installer/Contractor" and it's working perfectly now.
Formula looks like this:
Formula:
`Please Login or Register  to view this content.`

15. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

So... when you opened it, the formula array (the bit inside the {} in the middle of the formula) appeared as "\" it left here as a comma!! I thought it MIGHT have turned into a semicolon... but wasn't expecting that!!

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

16. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

Hold on again
I tested on bigger amount of data and the result turn out as decimal number (should be impossible)
Let me attached in 2 sec.

17. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

Bigger sample attached.

18. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

Oh dear... is it really more than an hour since I opened this!!??

Try it now. Too big to check manually... but it is returning an integer!!

19. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

I managed to make below formula work. It's very similar to the initial and the result is correct. The only difference is the part with the date.
Formula:
`Please Login or Register  to view this content.`

20. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

I see that you are using whole column references in your formula again.

21. ## Re: Counting unique values with multiple criteria. Not working when including Date in Form

ohhh. thought you wouldn't notice. lol

There are currently 1 users browsing this thread. (0 members and 1 guests)