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>
 

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
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,425
Office Version
  1. 2019
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
17,017
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

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,210
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.
 
Master Excel Bundle

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.

Forum statistics

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

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
Top