Excel/Create new sheet, summing columns within filtered list
please helps me in the following problem solution (VBA).
In the archives I have found a code of you which I cannot adapt, nevertheless (30th of December, 2010 - Summing columns within filtered list). I use Excel 2013.
I have a sheet with the following construction:
Sheet name: Data
Column A: IDNr
Column B: Item
Column C: Ertrag1
Column D: Ertrag2
Column E: Kosten1
Column F: Kosten2
IDNr and Item seem several times.
From the unique specimens of the columns A and B a new sheet (sheet Name: Kosten1) with all possible combinations of the columns A and B are formed. For every combination the sum from the matching column values (column E: Kosten1) are determined. Then the sheet construction looks thus:
Column A: IDNr (unique specimens from sheet Data column A, the first row A2)
Row 1: Item1 to Itemn (unique specimens from sheet Data column B, the second column B2 to Bn)
From the sheet Data the calculated sums of the single combinations should be copied in the sheet Kosten1 with formatting.
Under the column values I wish in each case the sum.
Thank you very much!
IDNr Item Ertrag1 Ertrag2 Kosten1 Kosten2
12345 AA001 500 750 10 160
345678 CB798 15
78 CB798 20
78 AA001 30
9876 BC1 5
12345 BC1 30
345678 CB798 40
12345 AA001 300 250 20 30
IDNr AA001 CB798 BC1
12345 30 30
78 30 20
Summe 60 75 35
I am not clear on what you expect the macro do to. Are you asking it to create the combinations and then sum up the data or are the combinations already in the sheet and you just need the sums. If you just need the sums and you have Excel 2007 or later, you can use the new Sumifs function which support summing for multiple criteria.
If you want to send me a sample file with a clearer description of what you want (even in you question, your numbers are all jumbled and hard to see what is under what column. )
you can send it to email@example.com
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here