"X" in a range returns value in another column

[NerdyOne]

I need to look in a range of numbers (B14:H14) named "TIPS_ONE" to see if any of the cells have an X in them (used to note that the person worked for tips that day). If any of the days are noted with an X then R14 has a value of $8 in it. The problem is, no matter if they work one day or seven, R14 can only equal $8.

I appreciate anybody's help on this one becuase it really has me scratching my head.

 

[Zathras]

You don't need VBA for that. Just put this formula in a cell:
[blue]

   =SUMPRODUCT(($B$14:$H$14="X")*8)

[/color]
or
[blue]

   =SUMPRODUCT((TIPS_ONE="X")*8)

[/color]

 

[NerdyOne]

I've tried that, and it works if the person only works one day out of the week, but if you "check off" more than one day in a week it multiplies each day by $8. Any suggestions?

 

[TonyJollans]

Hi NerdyOne,

If you just want $8 regardless of how many X's there are, try this in R14:

{color blue]

=IF(ISNA(MATCH("X",B14:H14,0)),0,8)

[/color]

Enjoy,
Tony