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.

 

[gd6noob]

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))

Thank you, way easier to understand..

 

[jasonb75]

Now that you can follow how the formula is working, if you look at the array in the formula, you will see that it matches the rows in the table, I've set alternating rows in a bold font to make them easier to identify.

=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}))

In the array, commas are used to separate the items in the row, a semicolon is used to start a new row.

 

[gd6noob]

Now that you can follow how the formula is working, if you look at the array in the formula, you will see that it matches the rows in the table, I've set alternating rows in a bold font to make them easier to identify.

=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}))

In the array, commas are used to separate the items in the row, a semicolon is used to start a new row.

I modified your formula to to refer today to user input date and works good.

=SUMPRODUCT($K$13:$K$17*(WEEKDAY(Dashboard!$I$3)={1,2,3,4;4,5,6,7;1,5,6,7;2,3,5,6;1,2,3,7}))

How would I count the number of people who are in but not scheduled. I tried edit the "=" before the array to "<>" but that was incorrect.

 

[jasonb75]

Sum all of the numbers then subtract the ones that should be there, with the previous formula in K4,

=SUM(K13:K17)-K4

 

[gd6noob]

Sum all of the numbers then subtract the ones that should be there, with the previous formula in K4,

=SUM(K13:K17)-K4

Is there a way without having to manually do that? Your sumproduct worked and thought there might be something similar....

 

[jasonb75]

That's not manual, it utilises the existing sumproduct formula in K4.

If the sumproduct formula in K4 tells you that there are 20 scheduled and there are actually a total of 26 in (sum of K13:K17) then the number of people not scheduled is 6.
26 (total) - 20 (scheduled)

 

[gd6noob]

You are right, thank you for all the help.