Making an internal function max range, = an external number

T

Travis Kunnen

New Member
Joined
Feb 24, 2016
Messages
21
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello All

My problem now is the below.

=COUNTIF($H$106:$H$605;"POS")

I am trying to count a range of data that contains POS, but the max range for each column changes each time. So I manually have to change the $605 to, 302, or 120, or 622.
Is there any way I can link the max range $H$605, to an external number in a cell, so that the 605 automatically changes when I change the external number?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Why not just a number big enough to cover any amount of data you might get. Something like
=COUNTIF($H$106:$H$10000;"POS")

However, if you want to specify that last row number in another cell (I have used cell A105) you could use this (You'll have to substitute ";" for my ",")
Excel Formula: 
=COUNTIF(INDIRECT("$H$106:$H$"&$A$105),"POS")
but given that INDIRECT is a volatile function I would use the following instead.

Excel Formula: 
=COUNTIF($H$106:INDEX($H:$H,$A$105),"POS")
 
Last edited:
Upvote 0
Ok, thank you.
I will update my details etc.
I cant do a big numbers because each column has a different max to count, where it ends at 0.

The formulas are not working.
The normal "When the first character is an =...."
 
Upvote 0
Thanks for updating your details. (y)

Travis Kunnen said:
The formulas are not working.
The normal "When the first character is an =...."
Click to expand...
What is your normal separator for arguments in functions? Do you need to swap ";" for where I have ","?

Here are the formulas working for me

24 03 01.xlsm
ABCDEFGH
10511022
106POS
107ABC
108
109POS
110DEF
111
112
113
114POS
115
TK
Cell Formulas
RangeFormula
C105C105=COUNTIF(INDIRECT("$H$106:$H$"&$A$105),"POS")
D105D105=COUNTIF($H$106:INDEX($H:$H,$A$105),"POS")
 
Upvote 0
The normal separator are both : and ;, so im not sure which go where..
I will try more extensively in a bit.

I put below a small edited section of what im trying to do and where im working. The cell ive colourd red is where the formulas are going, and the cell colored yellow is where the max range of the column will be put, for that column. If you follow down, you will see my manual entries of my functions.

fullData_genusLevel - Changed for Sorting - With fast Search - Copy.xlsm
GHIJKLMNOPQRSTUVWX
22123
23Equations >18
2478,50304981
2572,22222222
265
27Chemoautotroph_mixotroph0
2800
291266,66666667
30AOB00
3100
321372,22222222
33NOB00
3400
351372,22222222
36Anammox00
3700
381372,22222222
39Aerobic heterotroph00
40738,88888889
41527,77777778
42PAO00
43
44
45GenusChemoautotroph_mixotrophAOBNOBAnammoxAerobic heterotrophreadsrel_abund
46
47FermentimonasNTNTNTNTNTNTNTNTNTNTNTNTNTNTNT47610,201668926
48ClostridiumNot found33160,140460861
49RomboutsiaPOSNTNTNTNEGNTNTNTNEGPOSPOSNTNTPOSPOS20590,087216198
50TissierellaNTNTNTNTNEGNTNTNTNTNTNTNTNTNTNT11900,050406642
51HerbinixNTNTNTNTNEGNTNTNTNTPOSNTNTNTPOSNT1490,00631142
52ErcellaNTNTNTNTNEGNTNTNTNTPOSNTNTNEGPOSNEG2260,009573026
53AcetivibrioNTNTNTNTNEGNTNTNTNTPOSNTNTNTPOSNT1540,006523212
54AnaerocolumnaNot found5180,021941715
55MageeibacillusNot found820,003473399
56VariimorphobacterNot found380,001609624
57IgnatzschineriaNTNTNTNTNTNTNTNTNTNTNTNTNTNTNT27010,114410369
58MobilitaleaNTNTNTNTNEGNTNTNTNEGPOSNTNTNTPOSNT610,00258387
59ActinomycesNTNTNTNTVARNTNTNTNTPOSNTNTNTPOSNT5010,02122162
60ProteiniclasticumNTNTNTNTNEGNTNTNTNTPOSNTNTNTNEGPOS3050,012919349
61SphaerochaetaNTNTNTNTNTNTNTNTNTNTNTNTNTNTNT1940,008217553
62BacteroidesNTNTNTNTNTNTNTNTNTPOSNTNTNTPOSNT11290,047822772
63LascolabacillusNot found4880,020670959
64ErysipelothrixNTNTNTNTNTNTNTNTNTPOSNTNTNEGPOSNT6610,027998983
Sheet1
Cell Formulas
RangeFormula
W22W22=W23+105
W23W23=COUNTIF(W47:W64,">0")
X24X24=SUM(X47:X64)*100
X25X25=100-((X26/W23)*100)
X26X26=COUNTBLANK($V$47:$V$64)
X27:X42X27=((W27/$W$23)*100)
W28W28=COUNTIF($H$47:$H$64,"NEG")
W29W29=COUNTIF($H$47:$H$64,"NT")
W30W30=COUNTIF($I$47:$I$64,"POS")
W31W31=COUNTIF($I$47:$I$64,"NEG")
W32W32=COUNTIF($I$47:$I$64,"NT")
W33W33=COUNTIF($J$47:$J$64,"POS")
W34W34=COUNTIF($J$47:$J$64,"NEG")
W35W35=COUNTIF($J$47:$J$64,"NT")
W36W36=COUNTIF($K$47:$K$64,"POS")
W37W37=COUNTIF($K$47:$K$64,"NEG")
W38W38=COUNTIF($K$47:$K$64,"NT")
W39W39=COUNTIF($L$47:$L$64,"POS")
W40W40=COUNTIF($L$47:$L$64,"NEG")
W41W41=COUNTIF($L$47:$L$64,"NT")
W42W42=COUNTIF($M$47:$M$64,"POS")
 
Upvote 0
As I understand the requirement, it seems to be working for me. I've just entered a few of the formulas (& hidden some columns to make the mini sheet smaller).
I am a little confused though as you said the yellow cell (123) gives the max range yet your manually entered formulas are using row 64 as the max of the range.
My formulas are checking rows 47:123

24 03 01.xlsm
GHIJKLW
22123
2318
24
25
26
27
280
2912
300
310
3213
33
34
35
36
37
38
39
40
41
42
43
44
45GenusChemoautotroph_mixotrophAOBNOBAnammoxAerobic heterotrophreads
46
47FermentimonasNTNTNTNTNT4761
48ClostridiumNot found3316
49RomboutsiaPOSNTNTNTNEG2059
50TissierellaNTNTNTNTNEG1190
51HerbinixNTNTNTNTNEG149
52ErcellaNTNTNTNTNEG226
53AcetivibrioNTNTNTNTNEG154
54AnaerocolumnaNot found518
55MageeibacillusNot found82
56VariimorphobacterNot found38
57IgnatzschineriaNTNTNTNTNT2701
58MobilitaleaNTNTNTNTNEG61
59ActinomycesNTNTNTNTVAR501
60ProteiniclasticumNTNTNTNTNEG305
61SphaerochaetaNTNTNTNTNT194
62BacteroidesNTNTNTNTNT1129
63LascolabacillusNot found488
64ErysipelothrixNTNTNTNTNT661
65
TK (2)
Cell Formulas
RangeFormula
W28W28=COUNTIF($H$47:INDEX($H:$H,$W$22),"NEG")
W29W29=COUNTIF($H$47:INDEX($H:$H,$W$22),"NT")
W30W30=COUNTIF($I$47:INDEX($I:$I,$W$22),"POS")
W31W31=COUNTIF($I$47:INDEX($I:$I,$W$22),"NEG")
W32W32=COUNTIF($I$47:INDEX($I:$I,$W$22),"NT")
 
Last edited:
Upvote 0
Solution
I just didnt change the yellow cell value to the reduced cropped row data set.
The yellow cell will be the max range, regardless of the value.
 
Upvote 0
So my formulas are producing the correct results then?
 
Upvote 0
No, still either the = error, or., to many functions error, or highlights the POS and says error
Ive tried replacing your ":" for my ";", but still no. Then tried typing it out manually and putting in the correct , : ; while using your terms..still errors
 
Upvote 0
It working now.
Just had to play around with the colons and comma's.
=COUNTIF($H$136:INDEX($H:$H;$W$67);"POS")
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
369
GeneBF
G
A
  • Question Question
Take screenshots & simultaneously capture the link, place it into an Excel file which is linked to a FileMaker/Claris db.
Replies
3
Views
388
Dan_W
D
R
  • Question Question
progress a column range when copying
Replies
1
Views
137
Fluff
Fluff
M
  • Solved
Absolute Reference - Change absolute range without manual intervention
Replies
7
Views
492
Peter_SSs
Peter_SSs
A
  • Solved
UDF Not Recalculating?
Replies
6
Views
252
All_At_Sea
A

Forum statistics

Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444

Share this page

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