Formula for attendance daily summary

[Jayrey]

I've built an attendance sheet with a letter indicating the associates position (Picker, Shipper, Forklift) in column A, associate names in column B and the days of the week across (Monday, Tuesday etc.) across. I'm marking an "x" to those in attendance by day.

I'm looking for a formula to give me todays totals in attendance.

I want to say: if today is (day of the week), go to attendance sheet and count all associates with a P AND "x" under the corresponding (day of the week) column.

I'm looking for it to return a total count of associates from each position who are present and I want it to update to Todays day.

Thank you in advanced!

 

[awoohaw]

Hey jeyrey. Welcome.
Please post some data (sanitized to protect identities, if necessary) of your scenario, as well as expected outcomes.
Mr. Excel has a tool, xl2bb add in (link below) that allow you to do this. If you cannot use the add in, please post the data (label columns and rows please) using the TABLE posting tool (in each post's ribbon). If the data is not huge an image is okay, but it does mean the forum must completely recreate your scenario and that can lead to errors and is time consuming.

 

[Jayrey]

I've built an attendance sheet with a letter indicating the associates position (Picker, Shipper, Forklift) in column A, associate names in column B and the days of the week across (Monday, Tuesday etc.) across. I'm marking an "x" to those in attendance by day.

I'm looking for a formula to give me todays totals in attendance.

I want to say: if today is (day of the week), go to attendance sheet and count all associates with a P AND "x" under the corresponding (day of the week) column.

I'm looking for it to return a total count of associates from each position who are present and I want it to update to Todays day.

Thank you in advanced!

 

[Jayrey]

Here is a visual. I want to automate "Todays Actuals" to only give me attendance for the day of the week that match Today().

 

[kevin9999]

Book1
	A	B	C	D	E	F	G	H	I
1			Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	P	Happy				x
3	P	Bashful	x	x	x
4	P	Sneezy	x	x	x
5	P	Grumpy		x	x	x
6	D	Dopey	x		x
7	D	Sleepy	x	x	x	x
8	P	Doc	x	x	x
9	P	Snow	x	x	x
10
11			Staffing Count			Today's Actuals	Thursday
12			Pickers	6		Pickers	2
13			Drivers	2		Drivers	1
14			Total	8		Total	3
15
Sheet3

Cell Formulas
Range	Formula
D12	D12	=COUNTIF($A$2:$A$9,"P")
D13	D13	=COUNTIF($A$2:$A$9,"D")
D14,G14	D14	=SUM(D12:D13)
G11	G11	=TEXT(TODAY(),"dddd")
G12	G12	=SUMPRODUCT(($A$2:$A$9="P")*($C$1:$I$1=$G$11)*($C$2:$I$9<>""))
G13	G13	=SUMPRODUCT(($A$2:$A$9="D")*($C$1:$I$1=$G$11)*($C$2:$I$9<>""))

 

[Jayrey]

Book1
	A	B	C	D	E	F	G	H	I
1			Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	P	Happy				x
3	P	Bashful	x	x	x
4	P	Sneezy	x	x	x
5	P	Grumpy		x	x	x
6	D	Dopey	x		x
7	D	Sleepy	x	x	x	x
8	P	Doc	x	x	x
9	P	Snow	x	x	x
10
11			Staffing Count			Today's Actuals	Thursday
12			Pickers	6		Pickers	2
13			Drivers	2		Drivers	1
14			Total	8		Total	3
15
Sheet3

Cell Formulas
Range	Formula
D12	D12	=COUNTIF($A$2:$A$9,"P")
D13	D13	=COUNTIF($A$2:$A$9,"D")
D14,G14	D14	=SUM(D12:D13)
G11	G11	=TEXT(TODAY(),"dddd")
G12	G12	=SUMPRODUCT(($A$2:$A$9="P")*($C$1:$I$1=$G$11)*($C$2:$I$9<>""))
G13	G13	=SUMPRODUCT(($A$2:$A$9="D")*($C$1:$I$1=$G$11)*($C$2:$I$9<>""))

Thank you so much.

 

[kevin9999]

Thank you so much.

You're welcome, and thanks for the feedback