CountA for multiple words - need to exclude a few words and 2 need dividing by 2

[DumDeeDah]

Kaya,

We have a Leave planner chart that counts leave for the month.

All leave codes appear from drop down list.

Then those figures are added to a totals page.

Current Sum

=COUNTA(C14:AG14)-((COUNTIF(C14:AG14,"H1")+COUNTIF(C14:AG14,"H2"))/2) - please note H1 & H2 are half days which is why they are divided by 2.

However, we are now adding in a few other leave words (all in the dropdown list) and we do not want to count those in the leave total. WFH, TOIL, NP, PH

I would certainly apprecaite some advice on how to exclude multiple words.

Thanks

Dee

 

[Fluff]

Hi & welcome to MrExcel.
How many values are there that should be counted & how many that shouldn't?

 

[DumDeeDah]

Hi & welcome to MrExcel.
How many values are there that should be counted & how many that shouldn't?

There are 13 total. 4 need excluding, 9 need including - plus two of the 9 are only 0.5 (H1 & H2).

Thanks

 

[Fluff]

Ok, how about

=COUNTIFS(C14:AG14,"<>WFH",C14:AG14,"<>TOIL",C14:AG14,"<>NP",C14:AG14,"<>PH",C14:AG14,"<>")-((COUNTIF(C14:AG14,"H1")+COUNTIF(C14:AG14,"H2"))/2)

 

[DumDeeDah]

=COUNTIFS(C14:AG14,"<>WFH",C14:AG14,"<>TOIL",C14:AG14,"<>NP",C14:AG14,"<>PH",C14:AG14,"<>")-((COUNTIF(C14:AG14,"H1")+COUNTIF(C14:AG14,"H2"))/2)

Thanks but it is not totalling anything. Gave a Nil answer.

No error message so it must be close.

 

[Fluff]

In that case can you post some sample data showing the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.