Creating Range Arguments for use in function statements

B

Bill D.

Can any of you Excel experts 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.
 
F

Frank Kabel

Hi
try (assumption: A1:A8 contains your numbers)
=SUMIF(INDIRECT("R" & A1 & "C" & A2 & ":R" & A3 & "C" &
A4,False),"Z",INDIRECT("R" & A5 & "C" & A6 & ":R" & A7 & "C" & A8))
 
J

JE McGimpsey

Frank - did you actually test this?

As posted, it will give a #REF! error, since the second INDIRECT is
missing the a1 argument set to FALSE.

=SUMIF(INDIRECT("R"&A1&"C"&A2&":R"&A3&"C"&A4,FALSE),"Z",INDIRECT(
"R"&A5&"C"&A6&":R"&A7&"C"&A8,FALSE))

Note also that the A7:A8 arguments are irrelevant:

=SUMIF(A5:A8,"<>",B2:B5)

returns the same sum as

=SUMIF(A5:A8,"<>",B2)

so the formula could be shortened:

=SUMIF(INDIRECT("R"&A1&"C"&A2&":R"&A3&"C"&A4,FALSE),"Z",INDIRECT(
"R"&A5&"C"&A6,FALSE))
 
F

Frank Kabel

Hi JE
no I didn't test this :-(
Missed the FALSE part: So thanks for correcting this :)

gards
Frank Kabel
Frankfurt, Germany
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating Range Arguments for use in function statements 5
Variable Sumproduct Range 6
SUMIF data that falls within a range 3
Sumif using Arrays 2
IF function says too many nested 1
Difficult function question for columns 2
SUMIF result including values not specified in sum-range 2
How to Control a formula by a character in adjacent column on same 4

Top