Count Number of Rows Based on Criteria

Hi HansDouwe,


Thanks again for posting a solution to another one of my problems!

You bring up a good point ...


My apologies for not realising earlier that there are potential scenarios that I didn't think about.

I've attached another workbook to demonstrate the additional possibilities.


The word "unknown" can only exist in the given columns {5, 6, 7, 8, 19, 21, 22, 31}, and not in any of the other columns.

However, the word "unknown" exists by default in every cell in all given columns, until it is supposed to be manually changed to a piece of data.


For each row, the data entry starts in Column2, so Column2 should always have data.

So in the cases where, for example, B17 is empty and "unknown" appears somewhere else in the row, that row should be excluded from the COUNT calculation.

As soon as B17 is not empty, that row is included in the COUNT calculation, and it is then determined if it meets the criteria to be counted or not.


In terms of empty cells ... they can appear anywhere in the table (people not entering the data, accidentally deleting data, etc).

Row A10:AF10 now has an empty D10, but it should not be counted because none of the given columns {5, 6, 7, 8, 19, 21, 22, 31} are empty, nor do they contain the word "unknown".


The expected result for this workbook is still: 14.


My sincere apologies again for wasting your time with a problem that I did not think about thoroughly.

I hope it is now more clear!