sumproduct - cell reference

[eugene]

Hi,

In SUMPRODUCT, Is it possible to have the formula refer to another cell to
get a range value?
For example in the following formula
=SUMPRODUCT(--(A6:A40>=H5),--(A6:A40<H6))
the range varies from month to month - it is always in column A, always
starts at A6 but can go to A50, A45, etc.
This formula is used many times and I have been doing a find / replace to
change the range value. Just wondering if there is any way to put the value
of the end of the range in a cell and have the spreadsheet automatically
change its value in the formula (for example, put A50 or just 50 in cell A2
and have the formula pull the value from that cell? I think I have seen this
sort of thing done with with COUNTIF. Can something similar be done here?

 

[Max]

.. to put the value of the end of the range in a cell

INDIRECT is one way to get it done

Assuming the last row number will be entered in H4, eg in H4: 100
your expression could then be:
=SUMPRODUCT(--(INDIRECT("A6:A"&H4)>=H5),--(INDIRECT("A6:A"&H4)<H6))

 

[eugene]

thanks
--
eugene

INDIRECT is one way to get it done

Assuming the last row number will be entered in H4, eg in H4: 100
your expression could then be:
=SUMPRODUCT(--(INDIRECT("A6:A"&H4)>=H5),--(INDIRECT("A6:A"&H4)<H6))

 

[T. Valko]

Another one:

put the value of the end of the range in a cell

C1 = end of range

=SUMPRODUCT(--(A6:INDEX(A:A,C1)>=H5),--(A6:INDEX(A:A,C1)<H6))

In the above formula, if C1 = 0 or is empty the formula will return an
error.

Using COUNTIF:

=COUNTIF(A6:INDEX(A:A,C1),">="&H5)-COUNTIF(A6:INDEX(A:A,C1),">="&H6)

Or, you could use a dynamic range:

http://contextures.com/xlNames01.html#Dynamic

 

[Max]

Welcome, Eugene.
Hope you didn't miss the other possibilities in Biff's response