SumIF based on part of text in a cell

=SUMIF(A:A,"*Time Out*",B:B)

etc.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"slim" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> Is there any way that I can use the SUMIF function but looking for only
> a part of the text in a cell as a criteria. See the example below:
>
> On a raw data spreadsheet i have:
>
> A B C
> BB 120x600 Time Out 10,121 15
> BB 468x60 Time Out 9,979 2
> BB 120x600 PC Titles 33,823 114
> BB 468x60 PC Titles 35,507 49
> BB 120x600 ROS 5,726 15
> BB 468x60 ROS 5,712 2
>
> And on a formatted report sheet i have:
>
> A B C
> Time Out x y
> PC Titles x y
> ROS x y
>
> I would like to use a formula to populate columns B & C with the totals
> based on just 'Time Out' or 'PC Titles' etc without having to reformat
> the raw data first.
>
> Any help is greatly appreciated!
>
>
> --
> slim
> ------------------------------------------------------------------------
> slim's Profile:
http://www.excelforum.com/member.php...o&userid=28643
> View this thread: http://www.excelforum.com/showthread...hreadid=503279
>

Brilliant!! Thanks so much!

One other thing.

Would this also work with a Sumproduct, so i can work with more than one criteria?

No with SUMPRODUCT, it doesn't support wildcards, so you need a different
approach

=SUMPRODUCT(--(ISNUMBER(FIND("Time Out",$A$2:$A$20))),$B$2:$B$20)

just add another condition as normal.


Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"slim" <[email protected]> wrote in message
news:[email protected]...
>
> One other thing.
>
> Would this also work with a Sumproduct, so i can work with more than
> one criteria?
>
>
> --
> slim
> ------------------------------------------------------------------------
> slim's Profile:
http://www.excelforum.com/member.php...o&userid=28643
> View this thread: http://www.excelforum.com/showthread...hreadid=503279
>

Just to add to Bob's reply...

=Find() is case sensitive.

If you don't care about upper/lower case, you can use =Search() in that formula.

Bob Phillips wrote:
>
> No with SUMPRODUCT, it doesn't support wildcards, so you need a different
> approach
>
> =SUMPRODUCT(--(ISNUMBER(FIND("Time Out",$A$2:$A$20))),$B$2:$B$20)
>
> just add another condition as normal.
>
> Note that SUMPRODUCT doesn't work with complete columns, you have to specify
> a range.
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "slim" <[email protected]> wrote in message
> news:[email protected]...
> >
> > One other thing.
> >
> > Would this also work with a Sumproduct, so i can work with more than
> > one criteria?
> >
> >
> > --
> > slim
> > ------------------------------------------------------------------------
> > slim's Profile:
> http://www.excelforum.com/member.php...o&userid=28643
> > View this thread: http://www.excelforum.com/showthread...hreadid=503279
> >

--

Dave Peterson