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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael - 23 Feb 2008 16:19 GMT

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

Reply to this Message

Stan Brown - 23 Feb 2008 16:46 GMT

Sat, 23 Feb 2008 08:19:00 -0800 from Michael
<Michael@discussions.microsoft.com>:

> 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. :-)

Signature

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?

Reply to this Message

Stan Brown - 23 Feb 2008 17:18 GMT

Sat, 23 Feb 2008 11:46:45 -0500 from Stan Brown
<the_stan_brown@fastmail.fm>:

> Sat, 23 Feb 2008 08:19:00 -0800 from Michael
> <Michael@discussions.microsoft.com>:
> > 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'.

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

Signature

Reply to this Message

Ron Rosenfeld - 23 Feb 2008 16:50 GMT

>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

Reply to this Message

Michael - 23 Feb 2008 17:08 GMT

That's it, thanks a lot Ron!

> >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
[quoted text clipped - 6 lines]
> =sumif(a1:a100,"*"&C1&"*",b1:b100)
> --ron

Reply to this Message

Ron Rosenfeld - 23 Feb 2008 17:14 GMT

>That's it, thanks a lot Ron!

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

Reply to this Message