Using a range of numbers in COUNT-SUMIF(INDIRECT formulas

[ironsides]

=COUNTIF(INDIRECT("$AP19:$AP$9999"&$A$1),"9")

=SUMIF(INDIRECT("$AP$19:$AP$9999"&$A$1),"9",$BD$19:$BD$9999)

I am using these formulas successfully
but now I want to change the 9 to the range of 1:9

I tried several variations of the above, but to no avail.

thanks
Ironsides

 

[Aladin Akyurek]

What do you have in $A$1?

 

[ironsides]

What do you have in $A$1?

In cell A1



=HYPERLINK("http://www.nasdaq.com/symbol/"&E5536,$A$15)

 

[Aladin Akyurek]

Why do you concatenate this with every cell of "$AP19:$AP$9999"? What do you have in AP19 for example?

 

[ironsides]

Sorry Aladin

=HYPERLINK("http://www.nasdaq.com/symbol/"&E5536,$A$15) is in cell A19 which starts my data

Cell A1 is blank

 

[Aladin Akyurek]

Sorry Aladin

=HYPERLINK("http://www.nasdaq.com/symbol/"&E5536,$A$15) is in cell A19 which starts my data

Cell A1 is blank

Thus no need for a concatenate action!..

You say that you use:

=COUNTIF(INDIRECT("$AP19:$AP$9999"$A$1),"9")

successfully. Perhaps I'm missing something, but what does that mean? I mean how is this formula supposed to work?

 

[ironsides]

Col AP from lines 19 downward contains numbers from 0 to 500 not in order.
The current COUNT formula, =COUNTIF(INDIRECT("$AP19:$AP$9999"$A$1),"9")
successfully searches col AP , extracts the specified number 9, counts them
and places the answer in a separate chart

The current SUM formula, =SUMIF(INDIRECT("$AP$19:$AP$9999"&$A$1),"9",$BD$19:$BD$9999)
successfully searches Col AP for the number 9, and then, in col BD, searches each dollar amount
that corresponds to the number 9 in col AP, and places the answer in that same chart.

Since I have 500 different numbers in a sheet 5000 lines deep, of which 0 to 10 is prevalent, I need to have groups such as 11 thu 20 and 21 thru 30 etc
otherwise the chart would be too big.

Thanks Aladin

Matt

 

[Aladin Akyurek]

Hard to follow...

Suppose we have the following:

AP19 >> 5
AP20 >> 9
AP21 >> 19
AP21 >> 99

What would be the count result regarding "9"?

 

[ironsides]

If this is what you're asking me

AP # Cost Current Net roi
0 640 28,211 35,714 7504 0.266
1 398 9,206 10,738 1532 0.166
2 256 5,943 7,965 2022 0.340
3 247 6,721 8,706 1985 0.295
4 191 4,825 5,945 1120 0.232
5 173 4,087 5,164 1076 0.263
6 144 3,903 4,212 309 0.079
7 124 3,051 3,992 941 0.308
8 118 2,960 4,212 1252 0.423
9 99 2,652 2,908 256 0.097

 

[Aladin Akyurek]

What I was asking was the question in post #8 , not whatever you seem to forward in post #9 instead. I give up.