Solved

# formulas to simplify calculations

Posted on 2011-10-24
188 Views
Hi,

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.

Thanks. Data.xlsx
0
Question by:j1981

LVL 24

Accepted Solution

Here is one approach. C13 and across and down:

=SUMPRODUCT((\$C\$3:\$AX\$3=C\$11)*(LEFT(\$C\$4:\$AX\$4,1)=C\$12)*(\$C5:\$AX5))

C20 and across and down:

=SUMPRODUCT((\$C\$3:\$AX\$3=C\$19)*(LEFT(\$C\$4:\$AX\$4,1)="A")*(\$C5:\$AX5))*36+SUMPRODUCT((\$C\$3:\$AX\$3=C\$19)*(LEFT(\$C\$4:\$AX\$4,1)="B")*(\$C5:\$AX5))*40
0

LVL 50

Assisted Solution

For C20 this method would avoid doubling up the SUMPRODUCT

=SUMPRODUCT((\$C\$3:\$AX\$3=C\$19)*((LEFT(\$C\$4:\$AX\$4)="A")*36+(LEFT(\$C\$4:\$AX\$4)="B")*40),\$C5:\$AX5)

regards, barry
0

Author Closing Comment

Superb! Thank you both!!
0

## Featured Post

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.

#### Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!