Newbie Question in Excel Formula's

Question

I am creating a Excel sheet to calculate my company holidays over the annual period, and want it to calculate how many staff are off in any one day.

So I want it to calculate FD, HD, UL so I know how many people are away from the business on a particular day.

I have tried the help topics, but cant figure it out. PLEASE HELP!

FD = 1 person paid for 1 whole day annual leave

HD = 1 person paid for 1 half day annual leave

UL = 1 person unpaid for 1 whole day unpaid leave

RD = is not counted, but shown for planning purposes.

Any help much appreciate, and thank you in advance.

mathetes · Answer

You've had a number of views but no replies. That's unusual. I want to suggest, kindly, that what you've written is not clear. I'm sure it's clear to you, but it's somewhere on a scale from hard to impossible to figure out the connection between this sentence -- "I am creating a Excel sheet to calculate my company holidays over the annual period, and want it to calculate how many staff are off in any one day."-- and what follows.

That's for starters.

Then there's the fact that you've defined what each of the two letter codes mean, but what are we to do with them in connection with this sentence -- "I want it to calculate FD, HD, UL so I know how many people are away from the business on a particular day." -- and how does that relate to the very colorful chart using those codes?

Again, I think this is clear in your mind, but you're making a lot of assumptions that are behind the scenes, which makes it hard to impossible to offer help without totally taking a stab in the dark. Perhaps you could give a few examples, calculated manually -- and as our teachers used to say "Show your work" -- that illustrate what sorts of results you'd expect to see. We can work on the Excel way to get there, but right now we don't know where "there" is.

hansvogelaar · Answer

Assuming that the Monday codes are in F2:F10, enter the following formula in F11:
=SUM(SWITCH(F2:F10, "FD", 0, "HD", 0.5, "UL", 0, 1))
or
=COUNTA($A2:$A10)-COUNTIF(F2:F10, "FD")-COUNTIF(F2:F10, "HD")/2-COUNTIF(F2:F10, "UL")
Fill to the right to L11.

olufemio · Answer

This is a great practical Excel scenario — thanks for raising it!

I recently worked on something similar and wanted to share a working solution that covers both classic Excel and Excel 365 users.

Goal Recap

You want to calculate how many staff are off on a given day, where:

FD = Full Day leave = 1
HD = Half Day leave = 0.5
UL = Unpaid Leave = 1
RD = Rest Day = ignored.
Classic Excel Formula (Works in all versions)

This SUMPRODUCT formula counts staff off, applying the correct values for FD, HD, and UL.
If your Monday data is in cells D2:D10:

=SUMPRODUCT((D2:D10="FD")*1 + (D2:D10="HD")*0.5 + (D2:D10="UL")*1)

How to use:

Enter into the cell where you want the total for a specific day (e.g., D11 for Monday).
Adjust D2:D10 to cover all staff rows.
Drag the formula across to Tu, We, etc. — Excel will adjust the columns.

Breakdown by Category

=COUNTIF(D2:D10, "FD")
=COUNTIF(D2:D10, "HD") * 0.5
=COUNTIF(D2:D10, "UL")

Sum the results if you want a total with more detail.

Excel 365 Option: MAP + LAMBDA (Dynamic Arrays)

For Excel 365 users, here’s a modern version using LAMBDA and MAP:

Step 1: Create a custom function
(Optional – using Name Manager):

=LAMBDA(x, IF(x="FD", 1, IF(x="HD", 0.5, IF(x="UL", 1, 0))))

Step 2: Apply it with MAP:

=SUM(MAP(D2:D10, LAMBDA(x, IF(x="FD", 1, IF(x="HD", 0.5, IF(x="UL", 1, 0)))))

Bonus: Running total across one staff member’s row:
=SCAN(0, D2:J2, LAMBDA(a, b, a + IF(b="FD",1, IF(b="HD",0.5, IF(b="UL",1, 0)))))

Spreadsheet Screenshot Example:

Hope this helps you out!