SUMIF where the criteria is 'if contains a string'?

[Michael]

I would like to do something similar to: SUMIF(A1:A100,"<"&C1,B1:B100) but
the criteria should be 'the cell (within the range B1 to B100) contains the
string in cell C1'. How is it possible?

Thanks, Michael

 

[Stan Brown]

Sat, 23 Feb 2008 08:19:00 -0800 from Michael

I would like to do something similar to: SUMIF(A1:A100,"<"&C1,B1:B100) but
the criteria should be 'the cell (within the range B1 to B100) contains the
string in cell C1'. How is it possible?

=Sumif(A1:A100,C1)

Sometimes things are simpler than we think.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
A: Maybe because some people are too annoyed by top posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top posting such a bad thing?

 

[Ron Rosenfeld]

I would like to do something similar to: SUMIF(A1:A100,"<"&C1,B1:B100) but
the criteria should be 'the cell (within the range B1 to B100) contains the
string in cell C1'. How is it possible?

Thanks, Michael

Use wildcards:

=sumif(a1:a100,"*"&C1&"*",b1:b100)
--ron

 

[Michael]

That's it, thanks a lot Ron!

Use wildcards:

=sumif(a1:a100,"*"&C1&"*",b1:b100)
--ron

 

[Ron Rosenfeld]

That's it, thanks a lot Ron!

You're welcome. Glad to help. Thanks for the feedback.
--ron

 

[Stan Brown]

Sat, 23 Feb 2008 11:46:45 -0500 from Stan Brown

Sat, 23 Feb 2008 08:19:00 -0800 from Michael

=Sumif(A1:A100,C1)

Sometimes things are simpler than we think.

But this is, alas, not one of them. Sorry; I overlooked the
condition on range B1 to B100. Luckily, Ron has already given you a
solution.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
A: Maybe because some people are too annoyed by top posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top posting such a bad thing?

 

[Robert]

.... and from me too Ron - great tip - thanks.