Forum Discussion
Calculate from RAW data
I have a bank statement that I would like excel to add values that are reference to a text.
I have 50 rows and each row has a description of what was debited or credited to the account, for example - withdrawal from ATM (column 1) and $100 (column 2).
What I need help with is how to search a cell that has a specific word (eg. withdrawal) in a frase and adds the values of each rom where it is true for the specific word.
Been looking and searching for help for a few days and cannot find anything solid that resolves this issue. Thanks in advance for the help.
Thank you Damien, just one more issue. where u have the formula and result is 100$, is it possible to add the values of all the rows that show withdrawal? In your example it would be rows 6, 7 and 8 and total would be 240$.
FYI - Found this formula =SUMIF(A1:A28,"=*withdrawal*",B1:B28) that does the job also, but am curious if u can add sum to your formula also.
3 Replies
Hi Jorge
Hopefully I'm understanding right, I've put together a short example that may help. Please see attached source file and screen shots below.
Formula
=IF(ISNUMBER(SEARCH("withdrawal",$A$3:$A$9)),B3,"")
1. Replace $A$3:$A:9 with the description cell range in your spreadsheet.
2. B3 represents where your dollar amounts sit.
3. C3 is where I have put the formula to draw out any transactions that have the word withdrawl anywhere within it.
4. I have added a formula to total column C.
=SUM(C3:C9)
Here's the formula in action:
I hope that helps with your query.
Cheers
Damien
Thank you Damien, just one more issue. where u have the formula and result is 100$, is it possible to add the values of all the rows that show withdrawal? In your example it would be rows 6, 7 and 8 and total would be 240$.
FYI - Found this formula =SUMIF(A1:A28,"=*withdrawal*",B1:B28) that does the job also, but am curious if u can add sum to your formula also.
Hi Jorge
Looking at your formula, it does the job so I'd suggest sticking with the most simplest solution that works for you!
The difference between yours and mine is that I set mine up to show me which individual rows were withdrawals and then sum up, yours jumps straight to the total of withdrawals which gives the same total result.
I would suggest that you flag the post (best response) which gives you your solution so that other users can see (you can flag your own answer above if it is the preferred solution).
What you found looks good to me if you only want the total and nothing else!
Best of luck friend.
Cheers
Damien
