Help please - count cells in row with values but ignore certain words

[TrickyTrunk]

Hi all

I have searched on here and generally but can't find an answer to this. I am a beginner to Excel.

I am developing a work allocation spreadsheet that doubles as an annual leave diary. It will look something like this.

Employee Name	Total allocation	1st May	2nd May	3rd May
John Smith	1	A/L	12XX12345	DUTY

The 12XX1245 would be a reference number of the work that has been allocated on that day. A/L would mean that person is on leave. DUTY is a particular task that will also be listed on there.

Work would not be allocated the same day as leave (A/L) or DUTY. So if work is not allocated, the cell will be blank. So in example above, only one allocation has been made hence it's 1.

I want the Total Allocation column to count the number of allocated pieces of work. So far I have been using a forumla to count the cells in the row that has an entry but excluding cells with "A/L" but this means the formula is counting DUTY and I do not want that. I don't know how to exclude both A/L and DUTY though. My current formula:

=COUNTA(D9:AH9) - SUMPRODUCT(COUNTIF(D9:AH9,"A/L"))

 

[TrickyTrunk]

I managed to post that before finishing it. D9:AH9 is an example of the range of rows. I think it says what else it needs to - other than thank you very much in advance.

 

[etaf]

you could just add
=COUNTA(D9:AH9) - SUMPRODUCT(COUNTIF(D9:AH9,"A/L") - SUMPRODUCT(COUNTIF(D9:AH9,"DUTY"))

as a quick suggestion
a few other ways are possible
but will think about a shorter version

 

[TrickyTrunk]

I thought that might work but i've tried it and it just doesn't seem to work for me. It seems to then count every entry, even A/L or DUTY. I might be doing something wrong though.

 

[Fluff]

If your reference numbers always begin with a number try

=COUNT(FILTER(COLUMN(D9:AH9),ISNUMBER(LEFT(D9:AH9)+0)))

other wise

=COUNT(FILTER(COLUMN(D9:AH9),(D9:AH9<>"")*(D9:AH9<>"A/L")*(D9:AH9<>"Duty")))