CODEWITHSUNDEEP
excelfrequency
CLeonard
CLeonard

Reputation: 23

Count the number of dates that a criteria is met

I have a worksheet with over 30,000 entries in it of work logs submitted by a team of 15 individuals. I've broken this down using a range of countif/sumif/sumproduct formulas to try and summarise the data but am struggling to get one to work for counting the number of days a worklog was submitted by each member of staff over the period(baring in mind that each date the members of the team are working they are submitting multiple worklogs for various types of work).

I understand this is easily done with a pivot table but am trying to avoid using them for this as it will need to be presented/used by other members of staff who are not regular excel users. I would like to make this as simple for them as possible while being able to use the number of days they were at work to calculate averages etc.

I suspect I'm missing something easy here but any advice anyone can offer would be really appreciated.

The dates are in D41:D32038 and names of the workers that submit them in F41:F32308. Thank you.

Upvotes: 2

Views: 47

Answers (1)

EvR
EvR

Reputation: 3498

First Pivots are the way to go, but try this array-formula:

=SUM(--(FREQUENCY(IF(F41:F32308="name of worker",MATCH(INT(D41:D32038),INT(D41:D32038),0)),ROW(D41:D32038)-ROW(D41)+1)>0))

Change name of worker' to the name you're looking for The result should be the count of (unique) days for a worker, Close Formula with CSE

Upvotes: 1

Related Questions