Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel sheet with multiple rows in this format:

Column A|Column B
label   | value
--------|--------

header (merged cells)
labelA  |12
labelB  |5
labelC  |9

header (merged cells)
labelA  |13
labelD  |8

It's basically a list of labels and values split up over multiple different sections. In reality there are 70-80 labels and all labels are used 5-10 times. I need to create an output on a separate sheet in the following format:

Column A|Column B
label   | Total sum
--------|----------
labelA  |25
labelB  |5
labelC  |9
labelD  |8

Ideally the first sheet can be dynamically updated, with new labels reflecting immediately in the second sheet.

I'm sure I can create a macro to accomplish this, however I don't want to add macros if I do not have to. The data is not currently in a pivot table. Is there any way to accomplish this without those solutions?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

Getting the sum for the different labels is easily achieved using a SUMIF formula. Assuming your labels are in Column A of your output sheet, with the first label being in row 2, the formula for B2 would read:

=SUMIF(SourceSheet!$A:$A,$A1,SourceSheet!$B:$B)

– extend as needed. Getting all labels automatically is slightly trickier as you need to de-duplicate them. The following array formula will do that for the labels in rows 1 to 100 of your source sheet (adjust the scope as needed, replacing SourceSheet by the correct sheet name):

=IFERROR(
   INDEX(SourceSheet!$A1:$A100,
     SMALL(
       IFERROR(
         IF(
           MATCH(SourceSheet!$A1:$A100,SourceSheet!$A1:$A100,0)<ROW(),
           "",
           MATCH(SourceSheet!$A1:$A100,SourceSheet!$A1:$A100,0)
         ),
       ""),
     ROW())
   ),
 "")

Note this belongs on a single line in Excel (I’ve indented the formula here to make it a bit more readable) and that for it to work, you need to save it as an array formula (select the whole range you want your results in, input the formula in the formula bar – not in the cell! – and save it with Ctrl+Shift+Enter).

If the formula has been input correctly, starting with values like these:

Source Sheet screenshot

will return these results:

Destination sheet screenshot

As you can see, this will return your source sheet header rows too. They can probably be filtered out, but without details as to their exact contents, I can’t tell you how.

share|improve this answer
    
Well that's just awesome. It's a shame I just wrapped up a big ole macro, but I'm saving this for later. Hopefully future Googlers come across this! –  Whetstone Nov 3 '11 at 19:29
    
@Whetstone: that is what Ask Different is there for :). –  kopischke Nov 3 '11 at 19:42

The best thing you can do is to pivot it and once you add new data, just refresh the table. If you really don't like pivots, you can use sumif or sumproduct. Before you make the formula, set name ranges for your columns. It will be easier when you create the formula in another sheet.

=sumif(ColumnA, "labelA", ColumnB)

ColumnA & ColumnB will be the names of your ranges. It's just a sample

=sumproduct((ColumnA="labelA")*(ColumnB))

As for new labels, you can manually add them in the second sheet. I don't know how to add them automatically unless you make a macro. Hope that helps. :)

share|improve this answer

You can start with the sumif function. To total everything for labelA, use this function:

=sumif(Sheet1!A1:A9999, "labelA", Sheet1!B2:B9999)

You would need to add a function manually for each new label. I am not sure how to automatically add a total for a new label.

share|improve this answer
    
After investigating for about an hour I couldn't come up with anything and am now waist deep into creating a macro. Not the ideal solution in my books, but deadlines are deadlines. Thanks for the help! –  Whetstone Nov 3 '11 at 16:41
    
@Whetstone: For a formulaic solution see my answer (took a bit of time to test and write up). –  kopischke Nov 3 '11 at 18:45
    
That is only half an answer. See my solution for the other half :). –  kopischke Nov 3 '11 at 18:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.