Counting Formula (Count Weekends Off)

[uhcord98]

Hello all,

I have a scheduling tool I have made in Excel.
I attrached the document for your viewing.

I try to give each of my employees a weekend off per month.
A weekend off being defined off as a consecutive Saturday & Sunday.
For example, being off September 17th & 18th would be 1 weekend off.

I need a formula that counts the total amount of weekends off that each employee has.
I have already made a spot where I would like the formulas inserted.
I would like it to count weekends off if the cells on the consecutive Saturday & Sunday say "REQ OFF", "OFF" or "REQ VAC".

This is on sheet "Monthly."

[uhcord98]

Hello all,

I have a scheduling tool I have made in Excel.
I attrached the document for your viewing.

I try to give each of my employees a weekend off per month.
A weekend off being defined off as a consecutive Saturday & Sunday.
For example, being off September 17th & 18th would be 1 weekend off.

I need a formula that counts the total amount of weekends off that each employee has.
I have already made a spot where I would like the formulas inserted.
I would like it to count weekends off if the cells on the consecutive Saturday & Sunday say "REQ OFF", "OFF" or "REQ VAC".

This is on sheet "Monthly."

If it isn't clear on what I am trying to do let me know.

[uhcord98]

Anyone able to help?

[scottylad2]

I think I know what your looking ofr, some slight alterations would help. Fisrt off, the dates at the top of the screen what month are they from?

[daddylonglegs]

You could use simpler formulas for Saturdays and Sundays off, e.g. in J3 and K3

=COUNTIFS(A$3:A$200,A3,B$3:B$200,"*Off")

and

=COUNTIFS(A$3:A$200,A3,H$3:H$200,"*Off")

copy those formula down the column

Then for weekends try this similar version in I3 copied down

=COUNTIFS(A$3:A$200,A3,H$3:H$200,"*Off",B$4:B$201,"*Off")

Note that the range in red is deliberately "offset" 1 row from the other ranges

[uhcord98]

You could use simpler formulas for Saturdays and Sundays off, e.g. in J3 and K3

=COUNTIFS(A$3:A$200,A3,B$3:B$200,"*Off")

and

=COUNTIFS(A$3:A$200,A3,H$3:H$200,"*Off")

copy those formula down the column

Then for weekends try this similar version in I3 copied down

=COUNTIFS(A$3:A$200,A3,H$3:H$200,"*Off",B$4:B$201,"*Off")

Note that the range in red is deliberately "offset" 1 row from the other ranges

This works perfectly except it does not count "REQ OFF" or "REQ VAC".
I know I could just duplicate that formula and make it add those, but I also need it to work if there a combination off these.
For example, a "REQ OFF" & "OFF" = 1 Weekend Off
Or
"REQ VAC" & "REQ OFF" = 1 weekend off

or any combination of these 3.