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

[ironsides]

Formula help with this

=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, instead of using one number, in this case the number 9,
I want to use a range from 11:20

thanks
Ironsides

 

[KUYJS]

Looks like you want number between 11 to 20 instead of 9, if yes then try this:

=COUNTIF(AND(INDIRECT("$AP19:$AP$9999"&$A$1,">=11"),INDIRECT("$AP19:$AP$9999"&$A$1"<=20")))

 

[ironsides]

Thanks KUYJS, I copied and pasted your formula into a cell that is formated number, and the only thing that cell shows is the formula, no error message. I expected it to show the sum

 

[Aladin Akyurek]

11:20 means from row 11 to row 20. Is this what you meant?

 

[ironsides]

KUYJS yes an error message shows me that the second $A$1 is highlighted

 

[ironsides]

no, the 11 to 20 are individual numbers in cells in col AP.

 

[Aladin Akyurek]

no, the 11 to 20 are numbers in col AP.

Like these:

=SUMPRODUCT(ISNUMBER(MATCH(INDIRECT("$AP19:$AP$9999"&$A$1),$AP$1:$AP$11,0))+0)

=SUMPRODUCT($BD$19:$BD$9999,ISNUMBER(MATCH(INDIRECT("$AP19:$AP$9999"&$A$1),$AP$1:$AP$11,0))+0)

 

[ironsides]

Please let me clarify what I'm looking for:
starts row 19
the cells in col AP has numerals from O to 750
the cells in col BE has dollar numbers from $1 to $500

I need two things to happen:
I want to count all individual numbers that are in col AP
and I want the sum all of those individual numbers that have the dollar amount that are found in col BD

so if Col AP finds 50 numbers of 2 then i want the sum all those 2's that are found in col BD

So I'm counting and summing

 

[Peter_SSs]

Thread closed. Continued here

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Any bumps, clarifications, or follow-ups should be posted to the linked thread.