SUMPRODUCT with a Twist
 

Hi everybody,

I have this formula which works great ...

=SUMPRODUCT(--ISNUMBER(MATCH(C14:H14,$C$3:$H$3,0)))

.... but I would like to add something to it please.
If the answer produced by the above formula is 5, I would like it to
check cell "I3" to see if that number matches any of the numbers in
cells "C14:H14". If it does then enter "5+", otherwise enter the
original result of 5.

Thanks in Advance.
All the Best.
Paul

SUMPRODUCT with a Twist
 

=IF(SUMPRODUCT(--ISNUMBER(MATCH(C14:H14,$C$3:$H$3,1)))=5,SUMPRODUCT (--ISNUMBER(MATCH(I3,$C$14:$H$14,0))),SUMPRODUCT(--ISNUMBER(MATCH(C14:H14,$C$3:$H$3,1))))
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Paul Black" wrote:

Hi everybody,

I have this formula which works great ...

=SUMPRODUCT(--ISNUMBER(MATCH(C14:H14,$C$3:$H$3,0)))

.... but I would like to add something to it please.
If the answer produced by the above formula is 5, I would like it to
check cell "I3" to see if that number matches any of the numbers in
cells "C14:H14". If it does then enter "5+", otherwise enter the
original result of 5.

Thanks in Advance.
All the Best.
Paul