Count Number of Rows Based on Criteria

[Nevanox]

Hi!


Sample workbook is attached. The headers are just default; the real dataset has unique headers.


I have a table, which is 15 rows by 32 columns.


I need to count the number of rows which contain a cell that is: empty OR contains the word "Unknown"

but only if such a cell is in: Column 5 OR Column 6 OR Column 7 OR Column 8 OR Column 19 OR Column 21 OR Column 22 OR Column 31

and only if the cell in Column 2 is: not empty.


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 Column 2, so Column 2 should always have data in its respective row.

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 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 is: 14.


Any help is appreciated; thanks in advance!

[AliGW]

In the real dataset, is there something in those column headings that can be used as an identifier? A common word? I suspect that your sample data is a little bit over-simplified.

What is your expected result value?

[Nevanox]

The column headings in the real dataset are all quite unique; there are no commonalities between them.


In the case of my sample workbook, the expected result is:

14


All rows in the table have either an empty cell or contain the word "unknown" in one or more of the aforementioned specific columns, except for A10:AF10.

[sandy666]

like this?
with Power Query

CountRows

14

Please Login or Register  to view this content.

[Nevanox]

Hi Sandy!


Thanks for your message.

I would prefer a solution that uses normal Excel formulas; not Power Query or Pivot Tables.


I appreciate it anyway, though!

[HansDouwe]

In your sample workbook empty cells and cells with "Unknown" only exists in the given columns and never exists in other columns.

If that's always the case, please try

Please Login or Register  to view this content.

[sandy666]

I would prefer a solution that uses normal Excel formulas; not Power Query or Pivot Tables

No problem
Have a nice day

[Nevanox]

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!

[HansDouwe]

In this case it is not so easy to adjust the formula of post #6, I'll try adjust the formula tomorrow. Its a nice case.

[Nevanox]

I really appreciate you (and everyone else) taking a look at it!

Take care

[Bo_Ry]

Please try

=SUM(--BYROW(FILTER(Table1,ISNUMBER(XMATCH(Table1[#Headers],"column"&TEXTSPLIT("5, 6, 7, 8, 19, 21, 22, 31",", ")))),LAMBDA(a,OR(ISNUMBER(SEARCH("Unknown",a)),a=0))))

[Nevanox]

Hey Bo_Ry,

Thanks for your message!


Unfortunately, your solution is returning 21, when it should return 14.

Also, I can't rely on matching the headers, as the headers in the real dataset are all unique.


I appreciate your help, though!

[HansDouwe]

I think Bo_Ry's 21 is perfect, because you added 7 new rows to the table that are also counted.

[John Vergara]

Hi to all!

One option could be:

=SUM(BYROW(CHOOSECOLS(Table1,5,6,7,8,19,21,22,31),LAMBDA(x,N(SUM((x="")+(ISNUMBER(FIND("Unknown",x))))>0))))  

And yes... the result is 21. Blessings!

[Nevanox]

But the result should not be 21 ... the result should be 14.

Those 7 new rows should not be counted because the cell in Column 2 for each row is empty.

[AliGW]

Try this:

=SUM(BYROW(CHOOSECOLS(FILTER(Table1,Table1[Column2]<>""),5,6,7,8,19,21,22,31),LAMBDA(x,N(SUM((x="")+(ISNUMBER(FIND("Unknown",x))))>0))))

[Nevanox]

AliGW, that works perfectly, thank you.

And thank you to everyone else as well.

Sorry that this wasn't such a clear case ... I was confusing myself at times, but it's working now

[AliGW]

Glad to have helped.

If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[HansDouwe]

Thanks for the feedback and rep .