I am preparing a sheet for next year's vacation planning; however I've met a problem: You can take 1 full day of vacation, You also can take just half day. Making function that would substract just 1 from the 25 is no problem (=D5-COUNTIF(F5:AJ5; "D")) as can be seen in the picture below: [The first equation]

But as aforementioned, I also need to substract just half a day (which could be considered as value 0,5), if filled as HD. And on the top of that, I need to have the equation/function in one cell, so it would be done simultaneously.

So, if John decides to plan his vacation as "HD" on 2 January and "D" on 3 January, then the equation should be → 25-0,5-1=23,5.

Only solution I got after quite long research is this:

=D5-SUM(SWITCH(F5:AJ5; 1; "D"; 0,5; "HD"; "0,5")) 

I keep getting incorrect result and if I delete the "0,5", I will get #N/A.

I am attaching the link to the document → The spreadsheet

CodePudding user response：

The syntax for SWITCH is

SWITCH(expression, case1, value if case1, case2, value if case2,... default)

Here the case is either D or HD. Your arguments are switched. The correct formula is

=D5-SUM(SWITCH(F5:AJ5; "D"; 1; "HD"; 0,5; 0)) 

You can also use two COUNTIF like

=D5-COUNTIF(F5:AJ5; "D")-COUNTIF(F5:AJ5; "HD")/2

Divide the final count by 2