# Banking summary help

1. ## Banking summary help

Hi, I am looking to create a summary for cash, card and cheque payments from a table on a separate tab for each receipt codes.

I would basically just like a total for each of the receipt codes for each of the payment methods but cannot figure this out due to there being 3 different payment types so it's not just a simple IF formula.

I would like the summary to be on the table on the 'Banking Sheet 1'. All the info is generated from the 'Payment details' sheet. On the 'Payment Details' sheet, both the 'Payment method' and 'receipt code' columns contain a drop down list of options.

Thanks

2. ## Re: Banking summary help

Try

in F16
=SUMPRODUCT((LEFT('Payment Details'!\$D\$5:\$D\$1000,7)=\$E16)*('Payment Details'!\$B\$5:\$B\$1000=F\$15)*('Payment Details'!\$A\$5:\$A\$1000))
copy across and down as far as H25

3. ## Re: Banking summary help

Hi HK and welcome to the forum,

I built some fake data for you and then a Pivot Table from my data. See if it does what you want, in the attached..

Banking Summary Pivot.xlsx

4. ## Re: Banking summary help

Hey HK,

I do a banking workbook for my own in a slightly different way. I have a banking account and 2 credit card accounts that I use. Each of the 3 send me pdf files that I can download from their sites. Then I use a PDF program called Foxit PhantomPDF to convert all those monthly statements to Text files. I keep each accounts text files in different directories/folders. NOW - Using the new Power Query tool in Excel, I can pull in all the text files in a single folder. I build the power query to leave only 3 columns like you have; Date, Description, Amount. I pull all 3 accounts over to a single sheet and do an advanced filter and pivots on the data to analyze it.

The beauty of the above method is: each new month I simply add the statements text file into the folder and run the report again. The new month is included in the totals. No typing and Power Query works on all text files in the folder, even the new one I've added.

I haven't shown this to anyone yet as it is my personal account stuff but sometime in the future.

Yesterday I was going down the description list and could type in something like "Costco" and I have vba that converts that to "*Costco*" and puts this in the Advanced Filter area and filters my transactions to show records with Costco. I then can count, sum and even pivot by years. My wife doesn't like this ability much as I keep asking her "do you know who xxx" is? We send the 9.99 a month for the last 45 months.... She then needs to search the net and her LastPass list to see who the heck they are and realize we/she spends a lot of money on small stuff done a lot of times.

5. ## Re: Banking summary help

Absolute genius!!! Thank you,

6. ## Re: Banking summary help

Originally Posted by Special-K
Try

in F16
=SUMPRODUCT((LEFT('Payment Details'!\$D\$5:\$D\$1000,7)=\$E16)*('Payment Details'!\$B\$5:\$B\$1000=F\$15)*('Payment Details'!\$A\$5:\$A\$1000))
copy across and down as far as H25
Absolute genius, thank you!!!!

7. ## Re: Banking summary help

Originally Posted by Special-K
Try

in F16
=SUMPRODUCT((LEFT('Payment Details'!\$D\$5:\$D\$1000,7)=\$E16)*('Payment Details'!\$B\$5:\$B\$1000=F\$15)*('Payment Details'!\$A\$5:\$A\$1000))
copy across and down as far as H25

Sorry...me again! The formula works for the first few rows but then stops working after that and I can't see why
Really appreciate your help, thank you!

8. ## Re: Banking summary help

Initial thought would be that there might be an extra space in cells E21:E25. Otherwise it is hard to trouble shoot a screenshot, so I suggest that you upload the spreadsheets from which the screenshots were taken.
To upload a workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1