How do I reference a RANGE inside a Cell Formula?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JimP - 25 Sep 2006 02:31 GMT

Help ... I need to condense the following formula that's in multiple
cells. While this example is for illustrative purposes ... my true cell
formulas have reached the max character limit for a formula. So I'm
desperate to find an alternate way to calculate the total number of
specific shifts on a specific day of the month.

I'm hoping someone can show me how to rewrite the formula using a RANGE
that contains a member list consisting of both TEXT and NUMBERS:

THIS is the existing Formula:
{=SUM(IF($B$6:$B$75="F",IF(F$6:F$75=23,1,0)))+SUM(IF($B$6:$B$75="F",IF(F$6:F$75=24,1,0)))+SUM(IF($B$6:$B$75="F",IF(F$6:F$75=1,1,0)))+SUM(IF($B$6:$B$75="F",IF(F$6:F$75=2,1,0)))+SUM(IF($B$6:$B$75="F",IF(F$6:F$75="M",1,0)))}

If I had a RangeName: SHIFT_MID and
It's members: 23,24,1,2,"M"

I was thinking along the lines of:

{=SUM(IF($B$6:$B$75="F",IF(F$6:F$75=SHIFT_MID,1,0)))}

But this doesn't seem to work ...

Any ideas would be greatly appreciated ...

JimP

Reply to this Message

Biff - 25 Sep 2006 03:36 GMT

Hi!

Try this: (normally entered)

=SUMPRODUCT(--(B6:B75="F"),--(ISNUMBER(MATCH(F6:F75,I6:I10,0))))

Where I6:I10 = 23,24,1,2,M

Biff

> Help ... I need to condense the following formula that's in multiple
> cells. While this example is for illustrative purposes ... my true cell
[quoted text clipped - 20 lines]
>
> JimP

Reply to this Message