Creating Range Arguments for use in function statements

[Bill D.]

Can any of you Excel gurus tell me how to create a
reference range from cell values that can be used as
Function statement arguments? Take for example, the
following Excel function: SUMIF(A5:A10,"=Z",B5:B10)

This function sums all the numbers in the range from B5
to B10 where the corresponding rows of the adjacent
column A contains the text value "Z".

I could define (name) the ranges A5:A10 and B5:B10 as
rng1 and rng2, respectively. This would allow the above
SUMIF function to look like: SUMIF(rng1,"=Z",rng2), but
this is not what I'm looking for.

If I hve 8 separate cell values for the row and column
numbers of the start and end of each of these two ranges
(5, 1, 10, 1 and 5, 2, 10, 2), is there any way I can use
those values along with any appropriate Excel functions
to create cell ranges that can be used for the range
arguments inside the SUMIF statement? From what I can
determine, range arguments cannot be text values. So, for
example, I couldn't use the ADDRESS function.

It seems to me that I did this a number of years ago
using R-C formatted range references, but I can't
remember exactly how I did this. Any help you can give me
would be greatly appreciated.

 

[Vasant Nanavati]

Is this what you are looking for?

ActiveCell.FormulaR1C1 = "=SUMIF(R5C1:R10C1,""Z"",R5C2:R10C2)"

Or if you want to separate out the 8 numbers so you can substitute cell
references instead:

ActiveCell.FormulaR1C1 = "=SUMIF(R" & 5 & "C" & 1 & ":R" & 10 & "C" & 1 &
",""Z"",R" & 5 & "C" & 2 & ":R" & 10 & "C" & 2 & ")"

 

[Guest]

Thanks, Vasant.
I apologize for not stating the problem more clearly. I'm
sure your solution would work, but it's not exactly what
I'm looking for. I didn't want to write a separate VB
macro. I was hoping to construct a single-cell formula
for the SUMIF function directly in the worksheet w/o
having to run a separate macro. I would have to run the
macro to get it to update but a single cell function
would automatically update with cell changes.

Any other ideas?

 

[Vasant Nanavati]

The only solution I can think of is:

=SUMIF(INDIRECT(CHAR(1+64)&5&":"&CHAR(1+64)&10),"Z",INDIRECT(CHAR(2+64)&5&":
"&CHAR(2+64)&10))

which is kludgy and limited, but should work for columns A through Z.

 

[Frank Kabel]

Hi Vasant
I would skip the CHAR part of your formula and use the R1C1 notation
instead.
=SUMIF(INDIRECT("R" & 1 &"C" & 5 &":R"& 1 &"C" &
10,false),"Z",INDIRECT("R" & 2 &"C" & 5 &":R"& 2 &"C" & 10,false))

For the OP: Now you can replace the values 1,2,5 with cell references

 

[Vasant Nanavati]

Hi Frank:

Of course I agree. But the OP said he specifically wanted to use numeric
values for rows and columns (5,1,10,1,5,2,10,2), so I came up with this
awkward construction <g>.

Regards,

Vasant.