| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic multiple-condition array function with more than one n
Yep.
"Biff" wrote: Are all the dynamic ranges the same size? Biff "Gary F" wrote in message ... I think I figured this out. My problem is that the range's are dynamic, a la =OFFSET(WorkBacklog!$A$2,0,0,COUNTA(WorkBacklog!$A :$A),1) if I change them to static, they work fine. However, the above works okay in other situations.. Wierd. "Gary F" wrote: Here's something weird I can't figure out. The problem is that I need a cell to look at 1 dynamic range where the values in those ranges match dynamic/static values. The dynamic thing eliminates the D* functions (which seem to need static values like "Hat" rather than "A1"). So, if I have two named ranges nmeBacklogProjectColumn which is really $A$2:$A$2000 nmeBacklogProjectColumn which is really $Z$2:$Z$2000 and I do {=SUM((nmeBacklogProjectColumn=A11)*(nmeBacklogRow TypeColumn="Task")*1)} I get "#N/A" but if I do any of the following {=SUM((nmeBacklogProjectColumn=A11)*1)} {=SUM((nmeBacklogRowTypeColumn="Task")*1)} I get valid results and if I do this {=SUM(($A$2:$A$2000=A11)*($Z$2:$Z$2000="Task")*1)} I get the result I wanted originally However, the $2000 number is arbitrary - it's growing dynamically so I'd rather use a named range so I can change it one place and the sheet is updated. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| MEDIAN() as array function? | Excel Worksheet Functions | |||
| Using solver with function with multiple outputs | Excel Worksheet Functions | |||
| Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
| Array Function Question | Excel Worksheet Functions | |||
| Formula to list unique values | Excel Worksheet Functions | |||
Threaded Mode
