find common text in cell in column in sheet then return total amou
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 |
find common text in cell in column in sheet then return total amou
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 |
All times are GMT +1. The time now is 02:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com