FInding a cell with a particular word in, then putting the next row's figure into a new cell

[1Caspar]

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.

[#2]

Please disregard the words 'another company in column E' they weren't meant to be in the question. Thanks

[#3]

@1Caspar 

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

[#4]

@1Caspar 

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.

[#5]

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.

[#6]

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.

[#7]

@1Caspar 

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.