Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Summing partial strings.
For some time now I have been downloading bank statements. I have
columns for Transaction Date, Transaction Details, Amount, Balance, and one transaction per row and one worksheet per month. Transaction Details is a variable string, but there are some common elements, e.g Cheque No 0001, Cheque No 0002. What I would like to be able to do on each months worksheet is to automatically sum all of the outgoings to Cheques. So I need a function/formulae that will look in the Transaction Details column for 'Cheque', i.e. part of the string, and sum all corresponding Amounts in the adjacent column and put the answer in a 'Total Cheques' row that I will insert further down the sheet. I suspect I could use the 'SUMIF' function, but don't know how to do the partial string bit. What's the best/easiest way of doing this? Does anyone have a ready made formulae? I would then want to modify this for other partial strings, e.g. Cash Machine, on the same worksheet. TIA. |
#2
|
|||
|
|||
Assume your 'Cheque No 0001..." values are in the range A1:A100 and the
corresponding Amount's are in the range B1:B100: =SUMPRODUCT(--ISNUMBER(SEARCH("Cheque",A1:A100)),B1:B100) Just change the row/column ranges to match your data. -- Regards, Dave " wrote: For some time now I have been downloading bank statements. I have columns for Transaction Date, Transaction Details, Amount, Balance, and one transaction per row and one worksheet per month. Transaction Details is a variable string, but there are some common elements, e.g Cheque No 0001, Cheque No 0002. What I would like to be able to do on each months worksheet is to automatically sum all of the outgoings to Cheques. So I need a function/formulae that will look in the Transaction Details column for 'Cheque', i.e. part of the string, and sum all corresponding Amounts in the adjacent column and put the answer in a 'Total Cheques' row that I will insert further down the sheet. I suspect I could use the 'SUMIF' function, but don't know how to do the partial string bit. What's the best/easiest way of doing this? Does anyone have a ready made formulae? I would then want to modify this for other partial strings, e.g. Cash Machine, on the same worksheet. TIA. |
#3
|
|||
|
|||
With the descriptions in column A and the amounts in column B, use the array
formula (entered with Ctrl+Shift+Enter) =SUMPRODUCT(--ISNUMBER(SEARCH("cheque",A1:A4)),B1:B4) " wrote: For some time now I have been downloading bank statements. I have columns for Transaction Date, Transaction Details, Amount, Balance, and one transaction per row and one worksheet per month. Transaction Details is a variable string, but there are some common elements, e.g Cheque No 0001, Cheque No 0002. What I would like to be able to do on each months worksheet is to automatically sum all of the outgoings to Cheques. So I need a function/formulae that will look in the Transaction Details column for 'Cheque', i.e. part of the string, and sum all corresponding Amounts in the adjacent column and put the answer in a 'Total Cheques' row that I will insert further down the sheet. I suspect I could use the 'SUMIF' function, but don't know how to do the partial string bit. What's the best/easiest way of doing this? Does anyone have a ready made formulae? I would then want to modify this for other partial strings, e.g. Cash Machine, on the same worksheet. TIA. |
#4
|
|||
|
|||
Thanks David & Duke - it's easy when you know how.
|
#5
|
|||
|
|||
Hi,
You may try the following array formula (Ctrl+Shift+Enter) =SUM(IF(ISNUMBER(FIND(B10,$B$5:$B$8)),$C$5:$C$8)) where cell B10 contains "Cheque". You may change this to what you want later. $B$5:$B$8 contains "Transaction details" and $C$5:$C$8 contains "Amounts" Regards, Ashish Mathur " wrote: For some time now I have been downloading bank statements. I have columns for Transaction Date, Transaction Details, Amount, Balance, and one transaction per row and one worksheet per month. Transaction Details is a variable string, but there are some common elements, e.g Cheque No 0001, Cheque No 0002. What I would like to be able to do on each months worksheet is to automatically sum all of the outgoings to Cheques. So I need a function/formulae that will look in the Transaction Details column for 'Cheque', i.e. part of the string, and sum all corresponding Amounts in the adjacent column and put the answer in a 'Total Cheques' row that I will insert further down the sheet. I suspect I could use the 'SUMIF' function, but don't know how to do the partial string bit. What's the best/easiest way of doing this? Does anyone have a ready made formulae? I would then want to modify this for other partial strings, e.g. Cash Machine, on the same worksheet. TIA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard MATCH() breaks on long (?) strings | Excel Worksheet Functions | |||
Compare cells/columns and highlight matching text strings | Excel Worksheet Functions | |||
Cumulative Summing | Excel Discussion (Misc queries) | |||
Searching for Substrings Within Strings | Excel Discussion (Misc queries) | |||
Finding Partial Text in a Cell | Excel Worksheet Functions |