Forum Discussion

MPearson's avatar
MPearson
Copper Contributor
Jul 12, 2025

Newbie Question in Excel Formula's

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...
admin
Formulas and Functions
OlufemiO's avatar
OlufemiO
Brass Contributor
Jul 15, 2025

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!

Best,
Olufemi Olamoyegun

 



Resources