SUMIF SUM PRODUCT ? Specific text look up..

Hello Everyone.

Would appricaite some guidance please.. Im struggling to find the right formula..

I have a number of text fields in a table (column 1) such as :

Surrey fees - SAI - week 1
Kent fees - KNT - week 2
Surrey fees - SAI week 3
London fees - GL2 - week 1
London fees foor sales - GL2 - monthly

Against each line item is a price, in a seperate column ... such as ..

Surrey fees - SAI - week 1 £100
Kent fees - KNT - week 2 £ 1000
Surrey fees- SAI - week 3 £ 300
London fees - GL2 - week 1 £ 5000
London fees for sails - GL2 - monthly £ 20000

Im trying to add up all the values of rows with "SAI" (In capital letters) in the description, as a unique identifier.. ive tried =SUMIF(A5:A16,"*SAI*",B5:B16) - A5:A16 name range / B5:B16 Number range..

This kind of works... the issue im having is if a line of text has the letters "sai" any where else in the name description it pulls through the value... for example.... of a line has the word "sails" in the description it see this as a valid "SAI" entry... and counts it... add in my example an extra £ 20000 to the total..

Hi ,

Why not replace the criteria "*SAI*" by "*- SAI -*" ?

Narayan

You can use sum product.

Formula:

Please Login or Register  to view this content.

This won't work - it is looking specifically for the text "SAI" in column A, but those cells have phrases like "London fees for sails - GL2 - monthly" and "Surrey fees- SAI - week 3"

Pete

Is there some common character that indicates where the coding you want to look in starts? for example, you have a dash there. Are all the codes you want to look at starting with a dash and ending with a dash?

if so, why not just use text to columns to convert your data to a table, and then use a sumif

Thanks All.. Ill try these suggestions over the weekend.. Ruskie