Count number of colums that cantains numbers from a specific range

dj_mix

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

Some videos you may like

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
Joined
Jan 9, 2006
Messages
65
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
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Sep 24, 2014
Messages
14
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
Joined
Aug 23, 2010
Messages
16,393

ADVERTISEMENT

Maybe...

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

M.
 

Canapone

Active Member
Joined
May 10, 2007
Messages
463
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
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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
Joined
Sep 24, 2014
Messages
14
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,197
Messages
5,527,369
Members
409,758
Latest member
Smith79

This Week's Hot Topics

Top