unique count the number of users in month regardless their frequency

Good day your EXCEL-lencies,

I have a signup record that expected to grow up to row 400. On the top, there's a monthly tally for reports. Column B to count the number of boxes taken and Column C for the users picked up the boxes.

Users should be accounted as one appearence in a month, regardless re-appearance in the same month or different month.

I haven't found the generic formula to uniquely count the number of users regardless their frequency in a month. Please advice. Thank you in advance.

NOTE:
Timings, box numbering format and usenames are randomly generated. Any similarities with real subjects will be coincidence only.

I expect there is a simpler way using countifs but I'm a 2003 user so...

=SUM(IF(FREQUENCY(MATCH((IF(MONTH(A$20:A$40)=MONTH(A3),C$20:C$40,0)),(IF(MONTH(A$20:A$40)=MONTH(A3),C$20:C$40,0)),0),MATCH((IF(MONTH(A$20:A$40)=MONTH(A3),C$20:C$40,0)),(IF(MONTH(A$20:A$40)=MONTH(A3),C$20:C$40,0)),0))>0,1))-1

as an array formula (confirm with ctrl+shift+enter) in D3 and copied down will return the answers provided.

Hi,

I could achieve the desired result using a Pivot Table. Please check if this suits your requirement (file attached).

Regards,
Chandra


Please click on ‘ * Add Reputation’ if I was helpful in resolving the issue.

Thanks ragulduy. It works.

I copied the formula to the first month and pasted to the whole months, resulting #VALUE error. After that, I confirmed (Ctrl+Shift+Enter) cell-by-cell from January to December.

If pasted and confirmed all-together, it gives only one result as the month reference (A3) in the formula doesn't change.

=SUM(IF(FREQUENCY(MATCH((IF(MONTH(A$20:A$40)=MONTH(A3),C$20:C$40,0)),(IF(MONTH(A$20:A$40)=MONTH(A3),C$20:C$40,0)),0),MATCH((IF(MONTH(A$20:A$40)=MONTH(A3),C$20:C$40,0)),(IF(MONTH(A$20:A$40)=MONTH(A3),C$20:C$40,0)),0))>0,1))-1

So, I pasted all-together from Jan to Dec and let it gives #VALUE error. Then, the confirmation (Ctrl+Shift+Enter) was done one-by-one. And hey, it still works when I extended the range to row $400.

Attached file is the final tally with added dummies for May. Thank's again.

Hello Chandra,

I never learned Pivot Table before. With your example on this case, I will start learning it.

Thank you.