# Excel formula Problem

#### jhoncena33

##### New Member
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

#### Attachments

• Book33.xlsx
10.7 KB · Views: 6
=SUMPRODUCT((B2:B52="P")+(B2:B52="OD"))

Or,

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

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.