SUMPRODUCT using text qualifiers and multiple criteria

Question

I am attempting to use SUMPRODUCT with multiple criteria, one of which is a text qualifier.  One of the criteria I wish to use is to add up values in Col A where the text qualifier in the corresponding cell of Col B ends in ".un" or "-u".  When I use the foruma "--(Raw_Data!$S$1:$S$50000="*.un"), I find that I cannot use the wildcard as the formula interprets this value literally and NOT as the wildcard.  Any suggestions to develop a formula which will select values based on the ending value of a text string?  Thanks so much

Answer 1

I am attempting to use SUMPRODUCT with multiple criteria, one of which is a text qualifier.  One of the criteria I wish to use is to add up values in Col A where the text qualifier in the corresponding cell of Col B ends in ".un" or "-u".  When I use the foruma "--(Raw_Data!$S$1:$S$50000="*.un"), I find that I cannot use the wildcard as the formula interprets this value literally and NOT as the wildcard.  Any suggestions to develop a formula which will select values based on the ending value of a text string?  Thanks so much

You can try something like this...

(RIGHT(S1:S20,3)=".un")+(RIGHT(S1:S20,2)="-n")

--

Biff

Microsoft Excel MVP

Answer 2

Instead of the wildcard, use the right function - try...

"--(Right(Raw_Data!$S$1:$S$50000,3)=".un")

Answer 3

Many thanks -- that does it !!!