Countif staff name if date in range

[Aussie Grid]

Hi all, on a staff roster I would like to get a count of the shifts per individual for the pay period. Eg. If the pay period is 1/6/10 to 10/6/10 how many shifts is Allira working?
B & C are filled down from a master date & then formated "ddd" & "d". ie B1=A1, B2=B1+1, B3=B2+1 etc. Same for column C.
I need the name to refer back to the staff names range so that any future staff changes don't require a change of formula/ code etc.
Dates are set up this way so that 1 click can change the master date at the start of a new year and weekends etc are auto highlighted via con formating.

Any & all help appreciated
Aussie Grid
B C D
Fri 1 CAROLE
Sat 2 ALLIRA
Sun 3 ALLIRA
Mon 4 GLENIS
Tue 5 ALLIRA
Wed 6 GLENIS
Thu 7 JOSIE
Fri 8 GLENIS
Sat 9 ALLIRA
Sun 10 NATALEE
Mon 11 CAROLE
Tue 12 NATALEE
Wed 13 GLENIS
Thu 14 DANIEL

 

[xld]

Try

=SUMPRODUCT(--($B$2:$B$20>=--"2010-06-01"),--($B$2:$B$20<=--"2010-06-10"),--($D$2:$D$20>="ALLIRA"))

 

[Peter_SSs]

For Excel 2007+ I would use the formula in I2, otherwise I3.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	01/06/2010	Tue	1	CAROLE		Start	End	Name	Shifts
2		Wed	2	ALLIRA		01/06/2010	10/06/2010	Allira	4	Excel 2007+
3		Thu	3	ALLIRA					4	Excel 2003-
4		Fri	4	GLENIS
5		Sat	5	ALLIRA
6		Sun	6	GLENIS
7		Mon	7	JOSIE
8		Tue	8	GLENIS
9		Wed	9	ALLIRA
10		Thu	10	NATALEE
11		Fri	11	CAROLE
12		Sat	12	NATALEE
13		Sun	13	GLENIS
14		Mon	14	DANIEL
15
Shift Count

 

[Aussie Grid]

Grateful thanks to you both for your speedy replies, but the cigar goes to Peter as I'm running 2007, which I should have mentioned.

Again thank you for your assistance.

Aussie Grid

 

[rowlystravel]

rather than start a new thread as this is so closely related to my problem, whats the best way in 2007 to specify just a month and year i.e. May 2010 rather than a start and end date.. In 2003 i used sum product and TEXT "mmm" but i want to revamp my dashboard in 2007 as the countifs and averageifs functions are so simple to use

 

[Peter_SSs]

rather than start a new thread as this is so closely related to my problem, whats the best way in 2007 to specify just a month and year i.e. May 2010 rather than a start and end date.. In 2003 i used sum product and TEXT "mmm" but i want to revamp my dashboard in 2007 as the countifs and averageifs functions are so simple to use

Depends a bit how your month and year of interest are specified, but I suspect your existing SUMPRODUCT formula is something like H2, which is considerably simpler than the COUNTIFS in H3.

If this doesn't match what your sheet is like or what you are trying to do, please provide a small set of sample data and the expected result.

Excel Workbook
	B	C	D	E	F	G	H
1	05/10/2008	CAROLE		Month	Year	Name	Count
2	12/10/2008	ALLIRA		Nov	2008	Allira	2
3	19/10/2008	ALLIRA					2
4	26/10/2008	GLENIS
5	02/11/2008	ALLIRA
6	09/11/2008	GLENIS
7	16/11/2008	JOSIE
8	23/11/2008	GLENIS
9	30/11/2008	ALLIRA
10	07/12/2008	NATALEE
11	14/12/2008	CAROLE
12	21/12/2008	ALLIRA
13	28/12/2008	GLENIS
14	04/01/2009	ALLIRA
15
Count within Year

 

[xld]

rather than start a new thread as this is so closely related to my problem, whats the best way in 2007 to specify just a month and year i.e. May 2010 rather than a start and end date.. In 2003 i used sum product and TEXT "mmm" but i want to revamp my dashboard in 2007 as the countifs and averageifs functions are so simple to use

Maybe, but they are limited you cannot include range expressions, so continue with SUMPRODUCT.