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
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
