# Count number of colums that cantains numbers from a specific range

#### dj_mix

##### New Member
How do I count the number of Columns that contains values in a particular range??

I attached a sample, as always I appreciate your help..

 C D E F G H 1 0 5 54 52 2 2 32 23 23 21 15 44 65 65 32 66 84 84 12 225 23 23 8 How to count number of colums that cantains numbers from the range C3:H8 ?? output should read 4

<TBODY>
</TBODY>

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### JerseyRick

##### Board Regular
Under each column, use the following formula:

=IF(SUM(C2:C7)>0,1,0)

Then sum the results in whatever cell you want to show the value.

There is probably a better way to do this with VBA, but this is a quick answer.

#### Special-K99

##### Well-known Member
What's in C3? Could be anything.
What's in H8 as well?
You dont say.
Why should the output read 4? Between C3 (which doesnt appear on your diagram) and H3 (which also doesnt appear) I see 0 numbers not 4.

#### dj_mix

##### New Member
The range between C3:H8 is always numeric numbers, 4 respresents the column it's in for that range..

Vba code is also fine if it can be done without the helper row as mention by JerseyRick.

What's in C3? Could be anything.
What's in H8 as well?
You dont say.
Why should the output read 4? Between C3 (which doesnt appear on your diagram) and H3 (which also doesnt appear) I see 0 numbers not 4.

#### Marcelo Branco

##### MrExcel MVP

Maybe...

=SUMPRODUCT(--(SUBTOTAL(2,OFFSET(C3:C8,0,COLUMN(C3:H8)-COLUMN(C3)))>0))

M.

#### Canapone

##### Active Member
Hi All,

an attempt. The following formula must be confirmed with control+shift+enter

Code:
``=SUM(IF(FREQUENCY(IF(C3:H8<>"",COLUMN(C1:H1),0),COLUMN(C1:H1)),1,0))-1``

You could also try an helper row and copy below your data

Code:
``=--(COUNTA(C3:C8)>0)``

Just for sharing some ideas

Hope it helps

Edit: sorry, I did not mean to overlap. Regards

Last edited:

#### barry houdini

##### MrExcel MVP

You can use an "array formula" like this:

=SUM((MMULT(TRANSPOSE(ROW(C3:H8)),ISNUMBER(C3:H8)+0)>0)+0)

confirm with CTRL+SHIFT+ENTER

##### MrExcel MVP
The range between C3:H8 is always numeric numbers, 4 respresents the column it's in for that range..

Vba code is also fine if it can be done without the helper row as mention by JerseyRick.

You problem description is still insufficient. And calling on VBA won't help as long as it is not clear what the count problem is.

#### dj_mix

##### New Member
Marcelo, you formula appears to work great!! thanks
will test futher and see if it work 100%

Consider it solved, and thanks to all that helped out.

#### Marcelo Branco

##### MrExcel MVP
You are welcome and thanks for the feedback

M.

Replies
2
Views
35
Replies
3
Views
89
Replies
3
Views
30
Replies
29
Views
231
Replies
7
Views
52