Count Unique Values that Meet a Criteria

**DD1** · 03-08-2016, 10:40 PM

I need to count the number of staff that have been paid this year.
I have searched the forum and can see solutions that use an array formula.
However, the problem I have is that my file is in excess of 100,000 rows, and Excel tends to freeze when I enter the formula.
I have attached a spreadsheet with a small number of records.
The source data is in columns A-C.
The pivot table in columns E-F shows that the answer I want is 5. I've included the pivot table just to show the answer that I need.
Can anyone help by entering a formula in cell E13 that is based on columns B and C, bearing in mind that my actual file is very large?

Many thanks
David

**sandy666** · 03-08-2016, 11:33 PM

You can do this with a Pivot Table but add to data model first and you will get Value Field Setting = Distinct Count.
See att.

**FDibbins** · 03-08-2016, 11:51 PM

Not sure how you get 5 in E12 (if that was your expected answer?)

I did this with a helper column (I used D)with this in D2, copied down. You could put it elsewhere, and/or you can hide it...
=IF(COUNTIFS($A$2:A2,">"&EOMONTH(A2,-1),$A$2:A2,"<="&EOMONTH(A2,0),$B$2:B2,B2)>1,"",1)
copied down.

Then to get the count of EE's...
F4=COUNTIFS(B:B,E4,D:D,1,C:C,"Yes")
copied down

	E	F
4	A1	1
5	A2	3
6	A3	1
7	A4	1
8	A5	0

**DD1** · 03-09-2016, 06:16 PM

Thanks Sandy. I should have said that I am using Excel 2007 and unfortunately that option isn't available.
Thanks for taking the time to reply. I look forward to my workplace updating our version of MS Office

**DD1** · 03-09-2016, 06:19 PM

Thanks Ford. This is quite a nifty solution. The 5 in cell E12 is arrived at because there are 7 employees in total (i.e. 7 unique employee ID codes) and only employees A6 and A7 were not paid. I was hoping that I could simply enter a formula in a single cell, much like an array formula, but one that doesn't freeze my computer. From Sandy's reply it seems like there are other options in later versions of Excel.
Regards,
David