Counting unique values by date condition and another criteria

[mhessnm]

Hello, I have looked all over but I don't seem to find an answer to my particular question.

I am trying to count unique client id values by month and by another criteria, in this case gender.

Client ID	Date	Gender
1121	7/1/21	Male
1147	7/5/121	Female
1121	7/16/21	Male
7865	8/2/21	Female
5674	8/4/21	Male
7865	8/8/21	Female
9876	8/17/21	Female

In the table above, I should be able to find that there is 1 unique male and one unique female in July, and 2 unique females and 1 unique male in August, and that as I copy the formula down alongside a unique list of months, under columns for Male and Female, I would get those unique numbers.

I have been trying all kinds of different formulas and nothing is working. I seem to have trouble when I try to use the MONTH function in an array, regardless of whether I CSE or not. I have tried various ways of using UNIQUE and COUNT or COUNTA. I saw some formulas using FREQUENCY but I have not been able to make those work. I have also tried SUMPRODUCT and SUM(IF(COUNTIF...and also using COUNTIFS.

I have even tried a pivot table but while I can get a unique list of months, it still gives me a count of all the records within the month for gender.

I'm frustrated because I'm missing something and I don't know what it is. I also find working with dates as a criteria, particularly with counting, to be very difficult to work with in formulas.

I'd appreciate any suggestions.

 

[mhessnm]

Hello, sorry to have bothered you all. I finally found the answer. It was using UNIQUE with FILTER, and multiplying two criteria within FILTER, then counting the uniques:

=COUNT(UNIQUE(FILTER(range to filter, (filter criteria 1)*(filter criteria 2))))

If you read this, thank you in advance.

 

[Fluff]

It was using UNIQUE with FILTER,

Those two functions do not exists in xl 2016, so maybe you need to update your profile if you have them.

Also if there is a possibility that there will not be any rows that match the criteria, you would be better off using

=ROWS(UNIQUE(FILTER(range to filter, (filter criteria 1)*(filter criteria 2))))

count will return 1 even if there are no matches.

 

[mhessnm]

Those two functions do not exists in xl 2016, so maybe you need to update your profile if you have them.

Also if there is a possibility that there will not be any rows that match the criteria, you would be better off using

=ROWS(UNIQUE(FILTER(range to filter, (filter criteria 1)*(filter criteria 2))))

count will return 1 even if there are no matches.

Done, Fluff. I have 365. And thank you very much for providing an updated code. I appreciate your attention and suggestions.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[liaragon]

You're welcome & thanks for the feedback.

Hi,
I am working on a similar formula and think the answer is here:
What did you put in this formula to get the date criteria to specify a date range to count the data from?