How do I reference a RANGE inside a Cell Formula?

[JimP]

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

 

[Biff]

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