Elegant Solution to summarise progress information

[chullan88]

Hi,

I have a dataset in the attached format.
I am looking for an elegant and dynamic solution to obtain the summary information in cells N1:N9.


I have to prepare the same sheet for a lot of buildings with different no.s of basements and floors, so the process is tiring.

Thanks in advance.

[Bo_Ry]

Please try at

N1:N3
=SUMPRODUCT(($B$2:$B$20=MID(K1,7,7))*$D$2:$I$20*$D$3:$I$21)/SUMPRODUCT(($B$2:$B$20=MID(K1,7,7))*$D$2:$I$20)

N4:N7

Please Login or Register  to view this content.

N9
=SUMPRODUCT(MOD(ROW($D$2:$I$20)+1,2)*$D$2:$I$20,$D$3:$I$21)/SUMPRODUCT(MOD(ROW($D$2:$I$20)+1,2)*$D$2:$I$20)

[chullan88]

Thanks Bo_Ry...
Let me test it and then will update..

[chullan88]

Hi Bo_Ry, it works well..

I have one more query..

Can you provide formula to find total work done floorwise..

Eg. Total First Floor Work Complete

[Bo_Ry]

Floor name in K11:K16

N11
=SUMPRODUCT(($B$2:$B$20<>0)*($D$1:$I$1=K11)*$D$2:$I$20*$D$3:$I$21)/SUMPRODUCT(($B$2:$B$20<>0)*($D$1:$I$1=K11)*$D$2:$I$20)

[chullan88]

Thanks a lot...