Aug 14 2019 04:13 AM
Hello Excel Community,
I am looking for some help with a regular spreadsheet that I work with. It is a spreadsheet made up of data that I download. The arduous task is the long line of columns to departmentalise all the number transactions according to supplier name listed on the left. I have now created a dropdown list of all of the column headings and would now like to consolidate a total figure of all the number transactions (cumulative) from the listed columns into one cell next to the master 'list' used in the dropdown on a separate sheet (for each month).
thank you
Aug 21 2019 12:23 PM
Hi @lizi63 , do you happen a sample? I am not sure I follow.
Aug 21 2019 12:42 PM
@lizi63 It's hard to tell without seeing what you've got, but have you tried PivotTables? They'll let you quickly summarize data by supplier/date, etc.
Feel free to post some sample data to OneDrive, and give us the link. Someone will be able to take a look and point you in the right direction.
Aug 21 2019 03:10 PM
Hi @excelgeek
Thank you for your response. I have been trying various ways of doing this and can't quite get it. There are two tabs on the sample sheet. What I am hoping to achieve is to collate an individual list of values from sheet 1 as listed under the 'Amount' column (by associating the Categories column) and show the sum of those amounts on sheet 2 next to the corresponding Category on that list. I ha e tried a pivot table but it did not quote work. I hope the sample worksheet will help to explain it better.
Thanks again
Lizi
Aug 21 2019 03:20 PM
@lizi63 Unfortunately, I don't really see how the 2 sheets relate? When you download your data, is category listed in column E, and you're trying to move them to the Category columns (G:K), or vice versa?
Can you show an example that shows more detail from start to finish?
Aug 21 2019 03:25 PM
Hello@Smitty Smith
I'm sorry - I can see how that causes confusion. In fact, using column G-K and onwards was how it was previously done, and that just grew way too far horizontally. I tried to 'validate' column E so that it could correspond with a drop down with column A on sheet 2.
Aug 21 2019 03:50 PM
@lizi63 OK, see if the attached workbook gets you a bit closer. I left some notes on the transaction sheet, and you'll see a new Summary sheet that includes a few PivotTables.
Aug 21 2019 11:56 PM
@Smitty Smith thank you for your sample file. I tried it but its not quite what I am looking for. I have attached a further sample, and would appreciate a further view and comment, thank you.
Aug 22 2019 07:56 AM
Solution@lizi63 Provided you populate the Category column (E) in Sheet1 with actual categories, you can use:
=SUMIF('Sheet 1'!$E$2:$E$7,'Sheet 2'!A3,'Sheet 1'!$B$2:$B$7)
In column B on Sheet2 to sum the values by each category. Sample attached.
Aug 25 2019 11:01 AM
Hello @Smitty Smith thank you ever so much, this is exactly what I was looking to achieve. Very much appreciated.
best regards
Lizi
Aug 22 2019 07:56 AM
Solution@lizi63 Provided you populate the Category column (E) in Sheet1 with actual categories, you can use:
=SUMIF('Sheet 1'!$E$2:$E$7,'Sheet 2'!A3,'Sheet 1'!$B$2:$B$7)
In column B on Sheet2 to sum the values by each category. Sample attached.