Forum Discussion
FInding a cell with a particular word in, then putting the next row's figure into a new cell
I receive bank statements which show amounts received from Paypal, Amazon and Another company in Column E over many pages, and the next column shows the amount received.
I wish to not only calculate the total amount received from each, I also want to list them under their own heading in a separate column.
Although I can use A-Z to sort them into an order,This doesn't necessarily keep them in the date order they appear on the statement and also does not retain the bank statement in the order I go down it. I would like to create another table to the right, which extracts the information retaining the original.
Can anyone help me, I have gone to two columns at the end, and entitled one as Paypal, one as Amazon.
I would like to then put a formula in that looks in each Row under Column E and if the word Paypal appears, populate that cell with the amount from the same row in Column F going down in this way for all the individual entries on the bank statement. Likewise for the cells under the heading Amazon.
The only problem is, being a bank statement it doesn't just show the word Paypal or Amazon, there is a space after the word followed by other information which is different on every row.
I have tried If and SumIf, but am not very proficient in Excel and am hoping someone on here can advise me. I entered =SUMIF(E2:E273,"Paypal",E2:E273) and it didn't work.
6 Replies
The first part of your question simply requires the use of a wildcard criterion
= SUMIFS( Amount, Vendor, "*"& selected &"*" )
[the cell named 'selected' is assumed to contain "Paypal"]
To get a filtered list with inexact matches is harder. The start is to identify whether the selected string is to be found in each of the Vendors listed
= ISNUMBER( SEARCH( selected, Vendor ) )
For me, using Office 365 insider, I then simply nest the formula within the FILTER function
= FILTER( Amount, ISNUMBER( SEARCH( selected, Vendor ) ) )
In earlier versions of Excel the record numbers are first returned, then SMALL is used to filter out the mismatches and, finally, INDEX is used to look up the amounts.
- Peter thanks for the advice, I am unfamiliar with wildcards and will look into this as it is always good to increase knowledge with Excel. I will do my best to give this a go also.
Please find attached a copy of nabilmourad's workbook containing all the formula, including a copy of the original formula converted to employ defined names rather than direct references.
The final FILTER formula only works with Dynamic Array versions of Excel.
Hi
I noticed in your sample function provided, you are using the same range as a Criteria Range (for payment methods) and as Sum Range (for amounts)
I am attaching a sample file where I am using the function in L1 as follows:
=SUMPRODUCT(ISNUMBER(SEARCH($J$1,$E$3:$E$352))*$G$3:$G$352)
The condition is selected from a drop list as needed in cell J1.
Hope that helps
Nabil Mourad
- Nabilbourad, thank you for this, I can see from the formula you have put in the body of the text what this means and will look at the attachment regarding the drop list and give it a try. Many thanks for the time you have spent offering this help.
- Please disregard the words 'another company in column E' they weren't meant to be in the question. Thanks
