Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find max value in one column and return the value of corrosponding cell in different column | Excel Worksheet Functions | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
find text in column h and total the corresponding numbers if colum | Excel Discussion (Misc queries) | |||
Find certain text in a column and return statement | Excel Worksheet Functions | |||
pmt function in Excel does not return correct monthly payment amou | Excel Worksheet Functions |