contains formula
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel Training and help » 'Contains' formula

'Contains' formula

resolvedResolved · Medium Priority · Version 2007

Julia has attended:
Excel Advanced course
PowerPoint Intermediate Advanced course

'Contains' formula

Hello Best STL team,
Is there a formula for 'contains'?
If I want to find a cell that contains a word, for example?
Ideally where I want to get to with this is using it for vlookups and sums. So I have in column A a list of pruducts (e.g. Tetley Green 20s and PG Tips Extra Strong 80s and PG Tips Decaff 160s for example). I have the sales values in column B. I want (as a formula, not using filters) to find all the cells that contain PG Tips in that list and return the total sum of the sales values. Ditto for Tetley, and all the other brands. Sort of like what a pivot table would give me if I had a unique identifier, but I don't, as all the product names are different.

If this question doesn't make sense please let me know and I'll send you an excel sheet, if you can give me your email.

Many thanks,

Julia

Edited on Mon 29 Jul 2013, 16:49

RE: 'Contains' formula

Hello Julia,

You could use the DSUM function. The principle here is similar to Vlookup, as it searches a data range for numbers in a specified column to add up, based on criteria found in another column. See example below:

=DSUM(F5:G7,2,I5:I6)

F5:G7 describes the whole range (table) of data.

"2" is the column number in the range where the figures to be added up are found.

I5:I6 defines a separate criteria list you need to create, to be used in the formula. I5 would contain the column heading, e.g. Price, and I6 would contain the criteria, e.g. tetley. The formula would look for all occurrences of your criteria, regardless of what precedes or follows the word. So, for example, it would include Tetley 50s and
Tetley Green 20s in the result.

I have attached a sample Excel file which shows the data and formula.

I hope this answers your question. Please feel free to re-post if you need more assistance.

Kind regards,
Marius Barnard
Microsoft Office Specialist Trainer
marius@stl-training.co.uk
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk

Attached files...

DSUM.xlsx

Edited on Mon 29 Jul 2013, 17:51

RE: 'Contains' formula

Many thanks for your quick response, Marius.
Can I check that I need to include column headers for this and that the column header needs to be the same? So I need to have Product written above the total list and also written in your summarised list on the right hand side?

Also, when I replicate your formula, but I have more than one product name in the list on the right hand side, and I add $s in to the formula to keep the header, the formula doesn't work for any products below the top one.

Thanks for any help,
Julia

RE: 'Contains' formula

Hello Julia,

Yes, you need to include the column headings when you make your criteria list. Excel needs to know which columns you are referring to. Below the column headings, you can add as many items from your data as you like.

Instead of using $s in your formula, rather add more items or more columns to your criteria table, then include that in your formula. Excel will go and find whatever you include in the criteria table without needing $s.

Kind regards,
Marius Barnard
Microsoft Office Specialist Trainer
marius
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk

Read more: https://www.stl-training.co.uk/cms/forum.php?reply=33982 #ixzz2aVLMYZVe


RE: 'Contains' formula

Thanks, Marius, but I can't get it to work still.
Can you let me know your / an email address and I'll show you what I mean please?
Yours,
Julia

RE: 'Contains' formula

Hi Julia,

You can email the file to me at marius@stl-training.co.uk. I'll be happy to take a look at it.

Kind regards
Marius

RE: 'Contains' formula

Hello Julia,

We are clearing out any potentially unresolved forum questions, so just checking to see if you got this question resolved.

If not, please would you send your file to forum@stl-training.co.uk and we will take a look at it.

Kind Regards,

Richard

RE: 'Contains' formula

Thanks, Richard. I've just sent my query on DSUM to the forum email address you gave.
Julia

RE: 'Contains' formula

Thanks for your file Julia, Ill get this looked at for you.

Kind regards,

Richard


Attached files...

DSUM formula.xlsx

Edited on Wed 30 Oct 2013, 13:02

RE: 'Contains' formula

Hello Julia,

I have had another look at your example and have made some adjustments to it. Please have a look at the attached file to see what I did.

I created a separate criteria table entry for each type of product. This is to get a separate result for each product. Next, I added some $s to the first formula before copying it across to the other columns. This tells the formula to keep referring to the same data range when copying it.

Have a look at the yellow formula cells.

I hope this helps!

Kind regards
Marius

Attached files...

DSUM formula.xlsx

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

See also:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
excel vba course london and other training courses.

Excel tip:

Generating Random Numbers

To generate a random number in Excel use the = RAND() function.

The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)

Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.

Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.08 secs.