Count number of colums that cantains numbers from a specific range

dj_mix

New Member
Joined
Sep 24, 2014
Messages
21
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Maybe...

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

M.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top