• Status: Solved
• Priority: Medium
• Security: Public
• Views: 194

# 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.

Thanks. Data.xlsx
0
j1981
2 Solutions

Commented:
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

Commented:
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 Commented:
Superb! Thank you both!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.