Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic name reference in SUMPRODUCT | Excel Worksheet Functions | |||
SUMPRODUCT Criteria Via Cell Reference?? | Excel Worksheet Functions | |||
How can I use a cell reference in Sumproduct array formula? | Excel Discussion (Misc queries) | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Circular reference/SumProduct | Excel Worksheet Functions |