SUMPRODUCT with a Twist

[Paul Black]

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

 

[Guest]

=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))))