I need a formula

[FFOL8629]

I need a formula to add the number of days driver is assigned to any given route. I have about 70 different routes. I do not want it to count empty cells of cells containing the words  (OFF, VACATION, OUT, PERSONAL) I would like to the number of days to populate in the J column the set up is below.

is the example above the number of day working would add up to 5

[#2]

@FFOL8629 

That could be

=COUNTA(C4:I4) - SUM( COUNTIF(C4:I4, {"OFF","VACATION","OUT","PERSONAL"} ) )

[#3]

thank you, i tried it and it didn't work.


I should mention that C4:I4 are pick from drop down list.

[#4]

@FFOL8629 

Could you please check if attached file works in your environment. It has nothing but

[#5]

@FFOL8629 

In addition, it doesn't matter cell value was entered manually or taken from formula/drop down list.

[#6]

@Sergei Baklan 

[#7]

@FFOL8629 

Sorry, I didn't catch what your screenshot means. Is most right column generated by FORMULATEXT() or that's what you see after you enter the formula?

What about attached to previous post sample, does it work?

[#8]

I pasted the formula and thats what i see

[#9]

@FFOL8629 

• Make sure that the cells you paste to are not formatted as Text.
• Make sure that 'Show Formulas' in the 'Formula Auditing' group of the Formulas tab of the ribbon is not highlighted.
• Make sure that you pasted only the formula, without extra spaces or line feeds.