# 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 did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### 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
42
Views
2K
Replies
5
Views
349
Replies
4
Views
293
Replies
4
Views
473
Replies
0
Views
429

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,712
Messages
5,833,266
Members
430,200
Latest member

### 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