How To Do This Array Function
 

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

How To Do This Array Function
 

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

How To Do This Array Function
 

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

How To Do This Array Function
 

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 -

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 -