# Help with Array function

#### Hindustani

##### Board Regular

I have 4 rows and 4 columns let us say from A1:D4.
What I am trying to do is: to get the number of rows where atleast one cell is non-blank. How can I achieve it with array formulas?

For eg, in the above case, I have the second row all blanks, so my result would be 3 (3 rows have a value in atleast one cell)

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

a first approach:

=SUM(INDEX(--(COUNTIF(INDIRECT("A"&ROW(1:4)&":D"&ROW(1:4)),"<>")>0),0))

There are for sure better solutions to make it.

Let A2:D5 house the sample.

Control+shift+enter, not just enter:

=SUM(IF(MMULT(ISNUMBER(SEARCH("?",\$A\$2:\$D\$5))+0,TRANSPOSE(COLUMN(\$A\$2:\$D\$5)^0)),1))

Replies
2
Views
610
Replies
2
Views
2K
Replies
1
Views
247
Replies
9
Views
597
Replies
4
Views
514

1,196,043
Messages
6,013,055
Members
441,746
Latest member
ArtemisAlex

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back