Counting Unique Dates, between dates, with multiple criteria

[hpm23]

Hello there,

I'm stuck trying to deliver the correct answer from the data - there's got to be easier way!

I'm wanting to count the specific number of dates that a staff member has worked, between 2 separate dates. The data I have are invoices from all dates, for all time. It has the invoice date and staff name on it.

In the image attached - I want to count the number of dates Sally worked between 8 May and 11 May. The correct output would be 3, as she worked on the 8/5, 9/5 and 10/5 but not the 11/5.

I need a formula to count this as the dataset is enormous (10 years' worth) and I only need to count specific time periods.

Any help is greatly appreciated! Thank you.

 

[etaf]

something like
=ROWS(UNIQUE(FILTER(B2:B10000,(C2:C10000>="sally")*(B2:B10000>=F4)*(B2:B10000<=G4))))

Book1
	A	B	C	D	E	F	G	H
1
2		7/1/24	sally
3		7/2/24	sally
4		7/3/24				7/1/24	7/4/24
5		7/1/24						2
6		7/2/24	sally
7		7/3/24
8		7/1/24
9		7/2/24	sally
10		7/3/24
Sheet1

Cell Formulas
Range	Formula
H5	H5	=ROWS(UNIQUE(FILTER(B2:B10000,(C2:C10000>="sally")*(B2:B10000>=F4)*(B2:B10000<=G4))))

 

[Burrgogi]

Hello there,

I'm stuck trying to deliver the correct answer from the data - there's got to be easier way!

I'm wanting to count the specific number of dates that a staff member has worked, between 2 separate dates. The data I have are invoices from all dates, for all time. It has the invoice date and staff name on it.

In the image attached - I want to count the number of dates Sally worked between 8 May and 11 May. The correct output would be 3, as she worked on the 8/5, 9/5 and 10/5 but not the 11/5.

I need a formula to count this as the dataset is enormous (10 years' worth) and I only need to count specific time periods.

Any help is greatly appreciated! Thank you.

When posting your question on the forum, you should be as clear as possible where your data is located. From your screenshot, there is no way to tell what columns we're dealing with.

Assuming that the dates are located in col. B and the staff name is in col. C. Try this:

=COUNTIFS(C1:C100,"Sally",  B1:B100,">=5/8/2024", B1:B100,"<=5/11/2024")

Adjust B100 and C100 in the formula to the actual number of rows in your worksheet.

 

[hpm23]

something like
=ROWS(UNIQUE(FILTER(B2:B10000,(C2:C10000>="sally")*(B2:B10000>=F4)*(B2:B10000<=G4))))

This worked perfectly. Thank you!!

 

[etaf]

you are welcome
EDIT - Correction
the formula should have said = sally NOT >=
=ROWS(UNIQUE(FILTER(B2:B10000,(C2:C10000="sally")*(B2:B10000>=F4)*(B2:B10000<=G4))))

Book1
	A	B	C	D	E	F	G	H
1
2		7/1/24	sally
3		7/2/24	sally
4		7/3/24				7/1/24	7/4/24
5		7/1/24						2
6		7/2/24	sally
7		7/3/24
8		7/1/24
9		7/2/24	sally
10		7/3/24
Sheet1

Cell Formulas
Range	Formula
H5	H5	=ROWS(UNIQUE(FILTER(B2:B10000,(C2:C10000="sally")*(B2:B10000>=F4)*(B2:B10000<=G4))))