I want to search for the text in a cell from a column in another sheet, some
of the text will be common but not the same ie Daily Fees in cell, data sheet
will say Daily Fees 6 June 08, or D/E in cell and D/E 14589 in Data Sheet,
How to i create a formula that can search for the words i want in that
column, then return a sum total of everything matched from the amount column.

eg Sheet 1 Sheet 2 - Data Sheet
Description Total
Daily Fees $40.00 Daily Fees 1/6/08 $20.00
Daily Fees 2/6/08 $20.00
D/E $50.00 D/E 245878 $5.00
D/E 45784
$45.00

Assume source data in Sheet2's cols A and B is within A2:B10, where col A
contains text like this, eg in A2: Daily Fees 1/6/08, in A3: D/E 245878,
etc, and col B contains the corresponding amounts

In Sheet1,
In A2 down you have the key descript text, eg in A2: Daily Fees, in A3: D/E,
etc
Place in B2:
=SUMPRODUCT(--(ISNUMBER(SEARCH(A2,Sheet2!A$2:A$10))),Sheet2!B$2: B$10)
Copy B2 down to return the required results. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"leeona" wrote:
I want to search for the text in a cell from a column in another sheet, some
of the text will be common but not the same ie Daily Fees in cell, data sheet
will say Daily Fees 6 June 08, or D/E in cell and D/E 14589 in Data Sheet,
How to i create a formula that can search for the words i want in that
column, then return a sum total of everything matched from the amount column.

eg Sheet 1 Sheet 2 - Data Sheet
Description Total
Daily Fees $40.00 Daily Fees 1/6/08 $20.00
Daily Fees 2/6/08 $20.00
D/E $50.00 D/E 245878 $5.00
D/E 45784
$45.00