| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How To Do This Array Function
Worked perfect. Thanks! On Oct 4, 8:37 pm, ilia wrote: Array version: =SUM(($H$10:$H$16<"Done")*($H$10:$H$16<"")*($F$1 0:$F$16<$B$4)) Can do the same thing with SUMPRODUCT, but arrays are easier to monitor with formula evaluator. On Oct 4, 9:30 am, mjones wrote: On Oct 3, 4:26 pm, Pete_UK wrote: Hi Michele, you could try this array formula: =SUM(IF(($H$10:$H$16<"Done")*($F$10:$F$16<$B$4),$ F$10:$F$16)) committed with CSE, or this: =SUMPRODUCT(($H$10:$H$16<"Done")*($F$10:$F$16<$B$ 4)*($F$10:$F$16)) which does not need CSE. Hope this helps. Pete On Oct 3, 8:51 pm, mjones wrote: Hi All, I'm really guessing he =SUM(IF($H$10:$H$16<"Done",($F$10:$F$16<$B$4))) Of course it has the squiggly brackets. I'm trying to add the total number of items in F10:F16 that are less than B4 and are not showing as Done in column H. Help would be much appreciated. Thanks, Michele- Hide quoted text - - Show quoted text - Hi Pete, Actually, I need the total number of items so this worked by changing SUM to COUNT: =COUNT(IF(($H$10:$H$16<"Done")*($F$10:$F$16<$B$4) ,$F$10:$F$16)) Committed with CSE. Except that rows 15 and 16 are blank right now so I really had to change all the 16's to 14's. It's handy to keep some blank rows at the end for new enteries when the spreadsheet growns. Can the formula be made to say: Count if H10:H16 are not done and not blank? Thanks very much, Michele- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| array function | Excel Worksheet Functions | |||
| AND in an array function? | Excel Worksheet Functions | |||
| Array Function | Excel Worksheet Functions | |||
| Is there an array function or something like it? | Excel Worksheet Functions | |||
| Help with an array function | New Users to Excel | |||
Threaded Mode
