SUMPRODUCT but for text

[Anon9149]

Hi all,

With respect to the attached.

I need a formula in cell H4 that is capable of returning the entity code (column B) if the value in cell G4 is greater than or equal to the values in column D but less than or equal the values in column E.

I have already written a Sumproduct formula that is capable of executing this up to row 10, but being a mathematical formula it fails when extended to include row 11, which contains alphanumeric values (see cell B11).

Can someone assist? I can't think of a way to get this done, but I may be suffering from fatigue and just missing the obvious.

 

[bosco_yip]

Your formula : =SUMPRODUCT(($B$4:$B$10)*($D$4:$D$10<=$G$4)*($E$4:$E$10>$G$4))

1] If you want to use "*", try this:

=SUMPRODUCT(($D$4:$D$10<=$G$4)*($E$4:$E$10>$G$4)*N(IF({1},$B$4:$B$10)))

2] If you want to use ",", try this:

=SUMPRODUCT(($D$4:$D$10<=$G$4)*($E$4:$E$10>$G$4),$B$4:$B$10)

Regards

 

[pecoflyer]

Like this ( I added some values) ?