Macro to sum excel

Posted on 2011-10-21
Last Modified: 2012-05-12
I have a very large spreadsheet containing 38,000 rows and 93 columns.
There are 2,600 sections within those 38,000 rows.  These rows contain sections of up to 60 rows with the same identifying number in the first column.  I need to have each of these sections summed by column for 93 columns and included in a new worksheet as a separate row.
Attached file contains full spreadsheet and example of second worksheet containing first three sections.
Question by:gregfthompson

    Author Comment

    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Enter this formula in D7 and then copy it down and across

    =SUMIF('AUS CCD'!$A$7:$A$38703,'AUS POSTCODE'!$A7,'AUS CCD'!D$7:D$38703)

    Author Comment


    Looked good for the first three rows, but then all other rows showed zero.  

    The rest showed zero. What did I do wrong?

    And I also need the first postcode and first postcode name in columns A and B in thw main worksheet to be copied into the second worksheet.



    Author Comment

    Thanks I workd out the problem.  Whens there's no postcode and there's nothing to relate to.

    Can you provide a way to select first postcode and first postcode name in columns A and B in the main worksheet to be copied into the second worksheet?

    Then your formula will work.

    Thanks heaps,


    LVL 43

    Accepted Solution

    For column A here are two methods (Without VBA)

    Method 1: Advanced filter

    Data menu > Sort & filter group > Advanced
    Copy to another location
    Give the column A of the AUS CCD sheet
    Copy to some blank range
    Check unique records only

    when you click ok you can get the list of postcodes at the copy to range. You can copy this range to the Postcode sheet

    Method 2: Array formula

    Enter the first postcode (800) in A7

    use this array formula in A8 and copy it down
    =OFFSET('AUS CCD'!$A$1,MATCH(TRUE,OFFSET('AUS CCD'!$A$1,MATCH(A7,'AUS CCD'!A:A,0),0,1000)>A7,0)+MATCH(A7,'AUS CCD'!A:A,0)-1,0)

    For column b enter this formula in B7 and copy it down.

    Author Closing Comment


    Author Comment

    PS: How can I filter the Postcode and Subname lists so there is a suburbname against the single postcode?

    Author Comment

    PPS: Thanks I saw your final line -- all fixed .  Thanks heaps again!!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now