SOLVED

Consolidating figures from numerous columns into a single list

Highlighted
Occasional Contributor

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 

 

 

 

 

 

9 Replies
Highlighted

Hi @lizi63 , do you happen a sample? I am not sure I follow. 

Highlighted

@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.

Highlighted

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

Highlighted

@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? 

Highlighted

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. 

Highlighted

@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. 

Highlighted

@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. 

Highlighted
Best Response confirmed by Smitty Smith (Microsoft)
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.

Highlighted

Hello @Smitty Smith thank you ever so much, this is exactly what I was looking to achieve. Very much appreciated.

best regards

Lizi