Countifs but ignoring duplicates in 1 column

I need to count the number of client reviews completed by each team for each month. However, a client may appear multiple times (it will be the same date each time, a client wont ever have 2 different dates) but I only want the client counted once.

Please can you advise on how to use countifs but not counting duplicates for the client ref number or if there's another method.

Thank you.

Also posted here: https://www.mrexcel.com/board/thread...-once.1260439/

No expected results shown, so a guess. In G2 copied across to J2 and down:

=IFERROR(ROWS(UNIQUE(FILTER($B$2:$C$18,($B$2:$B$18=$F2)*(MONTH($C$2:$C$18)=MONTH(G$1&0))))),0)

Or maybe this?

=IFERROR(ROWS(UNIQUE(FILTER($A$2:$C$18,($B$2:$B$18=$F2)*(MONTH($C$2:$C$18)=MONTH(G$1&0))))),0)

Does either suggestion do what you want? If not, then what are your expected (results) for the sample dataset?

So, you came back to post a cross-post link (thank you), but didn't bother to acknowledge any of the suggestions made.

If you actually ENGAGE with us, we might be able to solve your problem.

Once again, there are NO expected results in your workbook, so until you tell us what you really want, it's all guesswork.

Please provide feedback on the three suggestions made so far.

Thank you.

Yes, it should do.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

If you are addressing me, then the OP has never actually told us what the expected results are. If they are happy with the suggestion, fine. If they hit any issues, they can come back and ask.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	F	G	H	I	J
1		April	May	June	July
2	ICT	3	3	0	0
3	BAU	2	4	0	0
4	Review	3	0	0	0

Sheet: Data

If you have a better suggestion, feel free to add it.

That wasn't clear as your post was immediately after mine and you did not directly address the OP.

The results you have shown in your post are not the results that my formula gives in the OP's sample file.

I got the same results as you, which were the expected results. Not sure where CARACALLA's results came from.

I did notice however that in error (although it actually helps for this) I had put one date as 2025. This date got counted in April's figures, which I guess is to be expected. However, I would like the results separated by year too. I changed the column header to Apr-24 but that didnt make a difference. Is there a way the column headers or formula can be tweaked please to identify by year. At the moment I only want 2024 but moving forward will want 2025. Thank you.

Provide an updated sample workbook and I'll sort it out for you.

If you generate the column and row headers ...

G1 - =TOROW(UNIQUE(TEXT(SORT(C2:C18),"mmm-yy")))
F2 - =SORT(UNIQUE(B2:B18))

... then you can use this:

=IFERROR(ROWS(UNIQUE(FILTER($A$2:$C$18,($B$2:$B$18=$F2)*(MONTH($C$2:$C$18)=MONTH(G$1))*(YEAR($C$2:$C$18)=YEAR(G$1))))),0)

I've set up post #17 to fit the updated workbook.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

OK, but please don't forget to sign off the thread.

Your post #17 does work. Thank you. However (and sorry to be a pain), I've been given an additional requirement which is to give data on a weekly basis as well as monthly. Could I add an updated sample set here or shall i do a new thread and close this one off?

Add it here, please.

Try this:

=IFERROR(ROWS(UNIQUE(FILTER($A$2:$C$18,($B$2:$B$18=$E2)*($C$2:$C$18>=F$1)*($C$2:$C$18<F$1+7)))),0)

You have enough variations on a theme now to be able to experiment yourself with others, I think.

Please Login or Register  to view this content.

This is not what your workbook seems to show, so I've set up another table handling what I think you mean. Again, though, you have enough now to be able to work out any further tweaks yourself. Obviously shout if you get into difficulty.

This thread really has been more than solved now, so please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

You're welcome.