formulas to simplify calculations

Posted on 2011-10-24
Last Modified: 2012-05-12

I have monthly key-wise(A1,A2,B1,B2) data available to me for different locations as shown in the attached Excel sheet. I want to have a way to consolidate this data into a higher level Key(A,B) by Month, and then to consolidate it further by applying other fixed numbers to the Key values.

In short, I want to be able to have some formulas for the yellow shaded regions.

Please help.

Thanks. Data.xlsx
Question by:j1981
    LVL 24

    Accepted Solution

    Here is one approach. C13 and across and down:


    C20 and across and down:

    LVL 50

    Assisted Solution

    by:barry houdini
    For C20 this method would avoid doubling up the SUMPRODUCT


    regards, barry

    Author Closing Comment

    Superb! Thank you both!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    779 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

    10 Experts available now in Live!

    Get 1:1 Help Now