How to mark a row if any of the cells in that row is not empty

I want to set the value of a cell as "Y" if any of the cells in that row is not empty. I am using

=IF(COUNTA(INDIRECT("B"&ROW()&":"&"Z"&ROW())),"","Y")

in the column A. By using an array formula I want to do this to all the rows in the sheet but it does'nt work simply by making the above as an array formula. How can i make the above an array formula or is there any other way. The array formula should be in A1 cell.

enter image description here

Amar

Posted 2016-08-18T15:51:13.473

Reputation: 1

Can you post a small example dataset together with your expected result? – XOR LX – 2016-08-19T06:22:54.617

I updated an image. The formula should be in A1 and if there are any values in a row from B to Z then the value of A in that row should be Y else "". – Amar – 2016-08-19T09:03:48.077

You're over-complicating this. If you need a row-by-row indicator, you need a row-by-row formula. You don't buy anything using an array formula, or the indirect function. @Sun's answer does what the question asks, and matches your example. You mention in a comment something about a column being hidden and not user updatable. It isn't clear what that condition is or how it relates, but if you think that somehow changes something, you need to explain that in the question or nobody will be able to help. – fixer1234 – 2016-08-20T02:25:02.987

Answers

I am counting how many cells in a particular row have data (except for column A that contains the formula). For row 1, the formula in A1 is:

=IF(COUNTIF(B1:Z1,"*"),"y","")

COUNTIF produces a count, but here it is just being used as a true/false test. Excel treats a result of zero as false and any positive number 1 or greater as true. If it is greater than 0, then the column A value is y. If not, leave it empty.

The * is a wildcard, meaning any data in the cell will include the cell in the count.

Since Row 1 has data in B1, C1, and D1, the value in cell A1 will be y. The same for the single value in row 12. Row 3 is blank because there is no data from B3 to Z3.

Sun

Posted 2016-08-18T15:51:13.473

Reputation: 5 198

I revised the answer. Let me know if you think it could be worded better. My brain is fried Friday afternoon. – Sun – 2016-08-19T23:30:30.843

You can mark non empty row 0like this

=if(counta(b2:z2)>0,"Y","")

This formula will count number as well as text and display the result accordingly

mahju

Posted 2016-08-18T15:51:13.473

Reputation: 85