• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

formulas to simplify calculations

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.

Please help.

Thanks. Data.xlsx
0
j1981
Asked:
j1981
2 Solutions
 
StephenJRCommented:
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
 
barry houdiniCommented:
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
 
j1981Author Commented:
Superb! Thank you both!!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now