.. 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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"eugene" wrote:
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?

--
eugene