Countifs formula for different days of the week?

[gd6noob]

Im trying to get some headcount going on and heres the issue.

There are different shiftcodes and the days they are scheduled to work.

As you can see from below img, current day is Friday, which means DB, DN, DC shifts are only scheduled to work for dayshift. I want to know if there is a single formula to sum up the total depending on what day today is into cell L4 or is this only manually done only?

As you see in the img above, column L9, my formula is

=COUNTIFS(Roster!$E:$E,Attendance!$A$1,Roster!$G:$G,"DA*")

, so this is also incorrect.

I think there is 2 ways to approach this but not sure how the formula would work.
1 - formula in L4 to sum L9 to L13 depending on day of the week.
2 - formula to count the number of people in L9 to L13 depending on the day, then I can sum L9 to L13 to have the correct data.

If anyone has any idea on how I can approach this, would greatly be appreciated.


Thanks in advanced.

 

[C Moore]

I'm not comprehending what is being summed. However you can match TODAY() in a range to pull the column to look at.
=MATCH(TODAY(),$B$3:$H$3)

 

[jasonb75]

None of that makes any sense, try posting your examples using XL2BB (click the button on the reply toolbar then follow the instructions).

Remember to include the relevant data and the expected results, remove anything that is not relevant to the question and fictionalise any personal / confidential data.

 

[gd6noob]

So lets say today is Monday. Only people scheduled to work is (from column A) DA, DC and DL. So I want to make a count of on the Roster (which lists everyone) who is suppose to work on a Monday.
If its Wednesday, only DA, DB is working.
So each shift code works on different days and I want to avoid manual input of formulas each day.

Is there a single formula that can do this?
If today = Sunday then sum L9, L11, L13.
If today = Monday then sum L9, L12, L13.
If today = Tuesday, then sum L9,L12, L13.
If today = Wednesday then sum L9, L10.
If today = Thursday then sum L10, L11, L12
If today = Friday then sum L10, L11, L12
If today = Saturday then sum L10, L11, L13

 

[QuestionBaker]

If what you wrote is correct, then try this
=IF(WEEKDAY(TODAY())=1,SUM(L9,L11,L13),IF(WEEKDAY(TODAY())=2,SUM(L9,L12,L13),IF(WEEKDAY(TODAY())=3,SUM(L9,L12,L13),IF(WEEKDAY(TODAY())=4,SUM(L9,L10),IF(WEEKDAY(TODAY())=5,SUM(L10,L11,L12),IF(WEEKDAY(TODAY())=6,SUM(L10,L11,L12),IF(WEEKDAY(TODAY())=1,SUM(L10,L11,L13))))))))

 

[gd6noob]

If what you wrote is correct, then try this
=IF(WEEKDAY(TODAY())=1,SUM(L9,L11,L13),IF(WEEKDAY(TODAY())=2,SUM(L9,L12,L13),IF(WEEKDAY(TODAY())=3,SUM(L9,L12,L13),IF(WEEKDAY(TODAY())=4,SUM(L9,L10),IF(WEEKDAY(TODAY())=5,SUM(L10,L11,L12),IF(WEEKDAY(TODAY())=6,SUM(L10,L11,L12),IF(WEEKDAY(TODAY())=1,SUM(L10,L11,L13))))))))

Thank you very much. This worked for me.

 

[jasonb75]

Another way

=SUMPRODUCT($L$9:$L$13*(WEEKDAY(TODAY())={1,2,3,4;4,5,6,7;1,5,6,7;2,3,5,6;1,2,3,7}))

 

[gd6noob]

Another way

=SUMPRODUCT($L$9:$L$13*(WEEKDAY(TODAY())={1,2,3,4;4,5,6,7;1,5,6,7;2,3,5,6;1,2,3,7}))

oh wow.. yea this also worked but I dont understand how to read it...

 

[jasonb75]

Is this easier to follow? The last part of the formula in my earlier reply mimics the weekday table below, days are numbered 1 (sunday) to 7 (saturday).

zgadson 21-7-20.xlsx
	G	H	I	J	K	L
7						16
8	Weekday					#of AA
9	1	2	3	4		9
10	4	5	6	7		11
11	1	5	6	7		5
12	2	3	5	6		0
13	1	2	3	7		4
Sheet3

Cell Formulas
Range	Formula
L7	L7	=SUMPRODUCT($L$9:$L$13*(WEEKDAY(TODAY())=$G$9:$J$13))

 

[QuestionBaker]

@jasonb75 I am amazed by how simple you made it look compared to the mess I wrote...