| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT - use last cell function for range
Hello,
I would like to use sumproduct function to determine how many criterias met in 2 columns. The range of value will change daily so I'd like to use the address(counta()) function as the close of ea range within sumproduct function. ie. sumproduct((Sheet1$A$1:address(counta(A:A),1)=a1)* (Sheet1$J$1:address(count(a:a),10)="no")) I accomplished this once many moons ago but apparently I've forgotten a step and cannot find the original formula that accomplished. Can anyone out there help? Thanks, Steven |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT - use last cell function for range
=SUMPRODUCT((Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA (A:A))=A1)*(Sheet1!$J$1:INDEX(Sheet1!$J:$J,COUNTA( A:A))="no"))
-- __________________________________ HTH Bob "SteveT" wrote in message ... Hello, I would like to use sumproduct function to determine how many criterias met in 2 columns. The range of value will change daily so I'd like to use the address(counta()) function as the close of ea range within sumproduct function. ie. sumproduct((Sheet1$A$1:address(counta(A:A),1)=a1)* (Sheet1$J$1:address(count(a:a),10)="no")) I accomplished this once many moons ago but apparently I've forgotten a step and cannot find the original formula that accomplished. Can anyone out there help? Thanks, Steven |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT - use last cell function for range
Thank You Bob, that did it ! BR, Steven
"Bob Phillips" wrote: =SUMPRODUCT((Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA (A:A))=A1)*(Sheet1!$J$1:INDEX(Sheet1!$J:$J,COUNTA( A:A))="no")) -- __________________________________ HTH Bob "SteveT" wrote in message ... Hello, I would like to use sumproduct function to determine how many criterias met in 2 columns. The range of value will change daily so I'd like to use the address(counta()) function as the close of ea range within sumproduct function. ie. sumproduct((Sheet1$A$1:address(counta(A:A),1)=a1)* (Sheet1$J$1:address(count(a:a),10)="no")) I accomplished this once many moons ago but apparently I've forgotten a step and cannot find the original formula that accomplished. Can anyone out there help? Thanks, Steven |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Value of cell to determine range in MAX Function | Excel Worksheet Functions | |||
| function problem regarding cell range | Excel Worksheet Functions | |||
| Function to determine if any cell in a range is contained in a given cell | Excel Worksheet Functions | |||
| function cell range limitations | Excel Worksheet Functions | |||
| Displaying value of specific cell within a range, with IF function...? | Excel Discussion (Misc queries) | |||
Linear Mode
