You are here:

Excel/Create new sheet, summing columns within filtered list


Hello, Tom,
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!

Sheet Daten     
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

Sheet Kosten1
IDNr    AA001      CB798      BC1
12345   30          30
345678          55
78      30         20
9876          5
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

Tom Ogilvy
About Excel
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


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.