• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array formulas - text and values

TFS2011

New Member
hi

I'm new to this board, and I can use excel quite well, but am hoping a few advanced users out there may be able to help me with a solution to my excel formula query... (I've spend mnost of today trying to find a solution!)


I have a database which has text in one column and values in the next column.


What I want to do is enter a formula which will look for a specific three characters in the text within the first column (so the three characters could be anywhere within the text), and then return a total of the values found in the second column, for all cells which contain those three specific characters.


So


aaa1 100

aaa2 100

aaas3 100

bbb1 100

bbb2 100

bbb3 100

xxaaa4 100

aaa5 100

xxaaa6 100


so using the (very basic) data example above, I'd like a formula that looks in col A for any cell containing aaa (anywhere) and then return me the total of the values in col B

(so in the example above the answer would be 600)


I've tried using an array formula with an if function, but this only works where cells contain the exact text I'm looking for (so 'aaa') rather than finding the three characters sitting together within any text in the first column.


It sounds like something which could be possible, but am not sure if I need multiple nested IF functions, or I need to combine a text forumla with a mathmatical one?


Hope I've explained it correctly, any help would be most appreciated.


Thank you (in advance!), lots

:)
 
assuming you data is in Col A1:B9 and your lookup value "aaa" is in C1

Try the following Array Formula

=SUMPRODUCT(1*(IFERROR(FIND(C1,A1:A9),0)>0),(B1:B9))

Enter with Ctrl Shift Enter
 
Non-array version usable in XL2003:

=SUMPRODUCT(1*(ISNUMBER(FIND(C1,A1:A9))),B1:B9)


Note that the FIND function is case-sensitive. If you don't want case-sensitive, use the SEARCH function.
 
Sorry for the delay in replying, thank you both for the answer. That's a great help! They both work. :O)
 
No probs, sorry for the slight delay! This board is an amazing wealth of information. :)


ps Hui, I hope you had a great birthday.


Thanks both.

TFS x
 
Back
Top