SumIF based on part of text in a cell

[slim]

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!

 

[Bob Phillips]

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

etc.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[slim]

Brilliant!! Thanks so much

 

[slim]

One other thing.

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

 

[Bob Phillips]

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)

 

[Dave Peterson]

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.