sumproduct - cell reference
 

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

sumproduct - cell reference
 

.. 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

sumproduct - cell reference
 

thanks
--
eugene


"Max" wrote:

.. 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

sumproduct - cell reference
 

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



--
Biff
Microsoft Excel MVP


"eugene" wrote in message
...
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

sumproduct - cell reference
 

Welcome, Eugene.
Hope you didn't miss the other possibilities in Biff's response
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"eugene" wrote in message
...
thanks
--
eugene