Using COUNTIF for split ranges

[Aeroica]

REGION: USA
EXCEL VERSION: 2013
MERGED CELLS: FALSE

The formula below used to calculate work strength each operational work day. If/when an employee calls in sick (SICK), is out for training (TR), or on Vacation (V), the use of that option will reduce the total employee count for that day by one (1). However, on rare occasion, an employee may go to a training event on their day off, and thus if the training code "TR" is used, it will reduce from the employee work strength when it really would not impact the work strength as they were already off.

PROBLEM:
I need to be able to use the following formula to count for a split range of cells

=SUM(COUNTIF(AC7:AC33,{"OJI","SICK","ML","AL","PL","FH","V","FL","FMLA","OIC","HOS","DO","OP","TR"})*{-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1})+AP68

Expected result: To have this formula function for two ranges of cells in the same column. AC7:AC20 and AC28:AC33

Thank you for any help you can offer

AEROICA

[Bo_Ry]

Is this work?

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Pl upload a sample file how you differentiate on training on duty or on day off.

[Aeroica]

Is this work?

Please Login or Register  to view this content.

Hello and thank you so much! This worked perfectly!

I actually tried a version of this but still had the "+AP68" at the back end of the formula. I don't understand why being at the front end would make a difference. Is this because once a condition is met, it stops calculating? If so, I thought that only applied to IF statements. Just really curious why putting the final calculation at the beginning made it work. I would not have thought to relocate that part of it. Nice job!

I also noticed that you didn't have to have the reduction of -1 for each choice. That is so nice, I thought you had to have the value specified. That's so much cleaner!

Thank you very much again!

[Bo_Ry]

Hi Aeroica,

Thanks for the rep.

I haven't try but put "+AP68" at the back should give the same result.

Please Login or Register  to view this content.

If this takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.