Excel formula Problem

[jhoncena33]

Greetings everyone,
I want to calculate P and OD in the given Excel. I also want to calculate holidays only if they fall between either P or OD at both ends. If there is VL at either end, then I do not want to calculate holidays. Please keep in mind that there may be more than one holiday back to back. i want to solve this problem only with formula.

example 01

20-05-2023​	P
21-05-2023​	Holiday
22-05-2023​	OD

total days 03

example 02

25-05-2023​	VL
26-05-2023​	VL
27-05-2023​	Holiday
28-05-2023​	Holiday
29-05-2023​	P

total days 01

thanks

 

[p45cal]

=SUMPRODUCT((B2:B52="P")+(B2:B52="OD"))

 

[bosco_yip]

Or,

=SUM(COUNTIF(B:B,{"P","OD"}))

 

[p45cal]

i want to solve this problem only with formula.

This could be quite a big ask. How easy it is depends on your version of Excel:
(a) what version of Excel are you using?
(b) why must this be formula only?

re:

I also want to calculate holidays only if they fall between either P or OD at both ends.

Not 100% sure what you're asking here but I'll have a stab. I need to be sure I'm getting the logic right.
In picture below, 2 tables. In each the third column tries to convert those holidays to P where those holidays have P or OD at both ends. If I've got this bit right then we can use the third column to do a count.
So have I got this right?
Note that the second table (on the right) has treated OD(TRAINING) on 29th June as plain OD; is this right?
It would have been a good idea not to delete the cells referred to in the data validation.