How To Do This Array Function

  • Thread starter Thread starter mjones
  • Start date Start date
M

mjones

Hi All,

I'm really guessing here:

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






- Show quoted text -
Click to expand...

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
 
Array version:

=SUM(($H$10:$H$16<>"Done")*($H$10:$H$16<>"")*($F$10:$F$16<$B$4))

Can do the same thing with SUMPRODUCT, but arrays are easier to
monitor with formula evaluator.
 
Worked perfect. Thanks!

Array version:

=SUM(($H$10:$H$16<>"Done")*($H$10:$H$16<>"")*($F$10:$F$16<$B$4))

Can do the same thing with SUMPRODUCT, but arrays are easier to
monitor with formula evaluator.










- Show quoted text -
Click to expand...
 
Back
Top