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

 

[Peter_SSs]

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.

You have given no indication why you are using INDIRECT.
From what you have written here, as far as I can see, these non-volatile formulas should do the same job.

=COUNTIF(AP19:AP9999,9)
=SUMIF(AP19:AP9999,9,BD19:BD9999)


Again guessing from your formulas, column AP may be numbers stored as text and BD ordinary numbers. If so, for the range problem try

=SUMPRODUCT((AP19:AP9999+0>=1)*(AP19:AP9999+0<=9))
=SUMPRODUCT(--(AP19:AP9999+0>=1),--(AP19:AP9999+0<=9),BD19:BD9999)

 

[Aladin Akyurek]

We have been there...

https://www.mrexcel.com/forum/excel-questions/1071671-using-range-numbers-count-sumif-indirect-formulas.html#post5147559

 

[Peter_SSs]

We have been there...

https://www.mrexcel.com/forum/excel-questions/1071671-using-range-numbers-count-sumif-indirect-formulas.html#post5147559

I can't see any indication there about why INDIRECT is being used, or suggestions without it.

However, thanks for the indication of that thread's existence. I have closed it so we don't have two going on the same topic.

 

[ironsides]

Please don't give up
the formula I'm looking for is instead of finding a single number interspersed in col AP the counting and summing the number that corresponds to it that is found it in col BD, adjusting my formula or a new formula,
with the substitution of a single number with a range of numbers.
I feel your frustrations as I am also perplexed. Anyway, I know you devoted a lot of time with this, I appreciate it,
I, thank you for you time and effort. Holiday seasons and upon us, and I wish you well.

Matt

 

[Peter_SSs]

Please don't give up
the formula I'm looking for is instead of finding a single number interspersed in col AP the counting and summing the number that corresponds to it that is found it in col BD, adjusting my formula or a new formula,

I suggested some for you to try.
What happened with them?

Edit: Here is my sheet with a small set of sample data. Column AP is numbers stored as text

What is wrong with the results or my sample data?

Excel Workbook
	AP	BC	BD	BE	BF	BG
19	5		1		Count	Sum
20	9		2		6	27
21	84		3
22	9		4
23	4		5
24	3		6
25	15		7
26	25		8
27	1		9
28
ironsides

 

[ironsides]

Within the past 10 years you have solved many of my problems, and also with the help other pro's here, so my data base is a work of art and something to be proud of. At the end of each day, within 20 minutes, I handle a great deal of data for almost a thousand stocks and do well. I take little credit for that tool we all developed. I learned alot from you all. But Perhaps I am not knowledgeable enough to understand what you are asking. I reviewed our current correspondence, and quite frankly, don't under stand your questions. Frustration for me yes, and for you. I also don't know where to go with this.
So, with this one need unsolved, I will continue to build my dB with help of you and others. Matt

 

[ironsides]

I am really embarrassed. I just went back to your =SUMPRODUCT((AP19:AP9999+0>=1)*(AP19:AP9999+0<=9))
=SUMPRODUCT(--(AP19:AP9999+0>=1),--(AP19:AP9999+0<=9),BD19:BD9999)
and they are spot on. It works. I don't what to day except I'm sorry to cause us both frustration.
Thanks Thanks Thanks Matt

 

[Peter_SSs]

I just went back to your =SUMPRODUCT((AP19:AP9999+0>=1)*(AP19:AP9999+0<=9))
=SUMPRODUCT(--(AP19:AP9999+0>=1),--(AP19:AP9999+0<=9),BD19:BD9999)
and they are spot on.

Well, some good news at last!

Also some lessons for the future I hope. When asking for help in the forum ..
- Don't get fixated on how to solve your problem if you haven't been able to solve it yourself.
- Read every response carefully and try any suggestions.
- Give feedback on suggestions so the helper knows they have been tried and what went wrong if they didn't work as expected.
- Try to answer any specific questions as directly & accurately as you can.
- Try to clarify things helpers seem unsure about (eg in this thread there have been some comments about why INDIRECT was being used and whether the values in column AP are actual numbers or numbers stored as text - neither issue has yet been clarified - what does =ISNUMBER(AP19) return? - & if AP values are actual numbers, the formulas required could be even simpler &/or more efficient than those suggested so far.)

 

[ironsides]

Yes Peter thank you and everyone who stayed tune to this drama which had a happy ending Matt

 

[Peter_SSs]

Yes Peter thank you and everyone who stayed tune to this drama which had a happy ending Matt

Did you miss my hidden question this time?

- Read every response carefully ...
- Try to answer any specific questions...
what does =ISNUMBER(AP19) return?