How To Do This Array Function

[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

 

[Pete_UK]

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

 

[mjones]

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 -

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

 

[ilia]

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.

 

[mjones]

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 -