Formula Question: Return a Value if a Cell Contains a Letter within a Text String

[Studk22]

Hi,

I have a cells drawing information from a table. In this table I have model numbers to depict products in one column. In another column I have sales totals for those model numbers. I would like to sum the sales for those model numbers if the model number contains an S.

Conveniently, these model numbers will always have the letter S at the end of the text string so I have been using this formula to try to sum the sales:

=SUMIFS(Sheet1!AQ10:AQ29991,(Right(Sheet1!D10:D29991,1)),"S")

However on future formulas I will need to sum sales based on other possible letters which will not always come last in the model number.

Even when I tried to do a trial formula to figure out the answer the following would not work:

A1 = 22

=IF(RIGHT(A1,1)=2,"Yes","No")

This returned the answer "No" which is incorrect.

Ideas?

 

[VoG]

That would be

=IF(RIGHT(A1,1)="2","Yes","No")

You could use

=ISNUMBER(FIND("A",A1))

to check for an A.

 

[Aladin Akyurek]

Hi,

I have a cells drawing information from a table. In this table I have model numbers to depict products in one column. In another column I have sales totals for those model numbers. I would like to sum the sales for those model numbers if the model number contains an S.

Conveniently, these model numbers will always have the letter S at the end of the text string so I have been using this formula to try to sum the sales:

=SUMIFS(Sheet1!AQ10:AQ29991,(Right(Sheet1!D10:D29991,1)),"S")

However on future formulas I will need to sum sales based on other possible letters which will not always come last in the model number.

Even when I tried to do a trial formula to figure out the answer the following would not work:

A1 = 22

=IF(RIGHT(A1,1)=2,"Yes","No")

This returned the answer "No" which is incorrect.

Ideas?

Shouldn't that formula (with SumIfs) run more like...

=SUMIF(Sheet1!D10:D29991,"*S",Sheet1!AQ10:AQ29991)

 

[Studk22]

Hi VoG,

Thank you for your help! I tried applying that formula to a sumif function and I can't seem to get it to work.

Lets say I have a table with 3 columns.

Column A = Product Names (Ambulance, Police Car, Firetruck, Mail Truck)

Column B = Product Model Numbers
(For Firetruck the model numbers are R69, R69F, R69S, R69FS, R79, R79F, R79S, R79FS, R89, R89F, R89S, R89FS, R99, R99F, R99S, R99FS)

Column C = Sales

I would like to sum the sales for all Firetrucks with the letter "S" in model number in which case I thought I could use the following formula:

=Sumif(C2:C100,A2:A100,="Firetruck",(ISNumber(Find("S",B2:B100))),="True")

I realize I could do a SumIf function in combination with the Or function and just put in all the model numbers but I have to do this for many more products with many more model numbers and if I could get a generic formula that I could copy and use over and over again by only changing the product name and the specific letter I'm looking for in the model number it would save me a great deal of time.

Thank you for your time VoG!

 

[Studk22]

Aladin,

That formula works, but what do I do if the letter (in this case "S") is not always the last letter in a text string in the cells I'm filtering?

What if the letter "S" sometimes is the last letter and sometimes the second to last letter?

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(C2:C100,--(A2:A100="Firetruck"),--(ISNUMBER(SEARCH("S",B2:B100)))

 

[Aladin Akyurek]

Aladin,

That formula works, but what do I do if the letter (in this case "S") is not always the last letter in a text string in the cells I'm filtering?

What if the letter "S" sometimes is the last letter and sometimes the second to last letter?

"*S*"

 

[Studk22]

Awesome, that's answers a lot of formulas that I needed.

Thanks Aladin!

By the way I love your movie!

 

[RebeccaC]

Try...

=SUMPRODUCT(C2:C100,--(A2:A100="Firetruck"),--(ISNUMBER(SEARCH("S",B2:B100)))

Whoa. The "--" is an "if"? I've never seen that before. Are there more of these fabulous function shortcut / add-in / thingies?

Thank you! This is great!

 

[Studk22]

Wow, that is awesome!

Thanks for the additional help Aladin!