How to Count Full and Half Vacation Days Separately in Excel
Question details
The user needs to calculate the total number of vacation days by separately counting full days (V) and half days (V.5) without them overlapping due to wildcard errors.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking employee vacation days using specific text codes for full and half days.
- Observed behavior
- A wildcard formula incorrectly counts both full-day codes ('V') and half-day codes ('V.5') as full days, resulting in an inflated vacation day total.
Ensure your vacation tracker uses consistent text codes (e.g., exactly 'V' for full days and 'V.5' for half days) without accidental trailing spaces in the cells.
Use Combined COUNTIF Functions for Exact Matches
Calculate the exact total by summing the count of full days and half of the count of half days.
Using a wildcard like 'V*' will count any cell starting with V as 1. By replacing the wildcard with exact matches, you prevent 'V.5' from being incorrectly counted as a full day.
Click on the cell where you want the total calculated vacation days to be displayed.
Start by typing the formula to count only exact full days: =COUNTIF(E28:NE28, "V") (ensure you replace E28:NE28 with your actual data range).
Append the second part of the formula to count half days and divide their total by 2: +COUNTIF(E28:NE28, "V.5")/2.
Press Enter. The complete formula =COUNTIF(E28:NE28,"V")+COUNTIF(E28:NE28,"V.5")/2 will now output the precise number of total days taken.

Use SUMPRODUCT for Multiple Criteria
An alternative array-style formula that handles distinct text criteria and their respective values simultaneously.
Track Vacation Days Effortlessly with WPS Spreadsheet
WPS Office Spreadsheet fully supports all standard Excel functions, including COUNTIF and SUMPRODUCT. You can easily build, track, and manage complex employee leave schedules flawlessly.
- 1. Open your tracker: Launch WPS Spreadsheet and open your employee vacation tracking file.
- 2. Select the total cell: Click on the specific cell designated for the vacation days total.
- 3. Type the combined formula: Enter =COUNTIF(range,"V")+COUNTIF(range,"V.5")/2 into the formula bar.
- 4. Press Enter: Hit Enter to instantly calculate the correct vacation time without wildcard errors.

Frequently Asked Questions
Why does COUNTIF("V*") count half days as full days?
The asterisk (*) is a wildcard that represents any sequence of characters. A formula searching for 'V*' will match 'V', 'V.5', 'Vacation', or any text starting with V, counting each matching cell as exactly one full instance rather than reading its intended fractional value.
Can I use COUNTIFS instead of COUNTIF for this calculation?
While COUNTIFS allows for multiple criteria across different ranges, counting multiple distinct 'OR' values in the exact same range (like 'V' or 'V.5') is much simpler and more reliable by adding two separate COUNTIF functions together.
How do I handle case sensitivity in COUNTIF?
The COUNTIF function is fundamentally not case-sensitive. It will count both uppercase 'V' and lowercase 'v' equally, so you do not need to create a separate formula to account for lowercase entries.
What if I also have quarter-days marked as 'V.25'?
You can easily extend the same formula structure by adding another COUNTIF statement and multiplying it by 0.25 (or dividing by 4). The expanded formula would look like: =COUNTIF(E28:NE28,"V") + COUNTIF(E28:NE28,"V.5")/2 + COUNTIF(E28:NE28,"V.25")/4.




