0

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

  • Can you post a small example dataset together with your expected result? – XOR LX Aug 19 '16 at 6:22
  • 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 Aug 19 '16 at 9:03
  • 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 Aug 20 '16 at 2:25
1

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.

enter image description here

  • I revised the answer. Let me know if you think it could be worded better. My brain is fried Friday afternoon. – Sun Aug 19 '16 at 23:30
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.