SUMIF FORMULA or SUMPRODUCT FORMULA

[Nandakumar]

Hi,

I am having the below data with Premise,Premise type and apportionment basis for premise type of amount.

I want report on Subcity type for premise type.



Attached excel sheet for review.

Thanks in advance.

Regards,

 

[NARAYANK991]

Hi ,

I am not sure that this is what you want. See if it is OK.

Narayan

 

[Nandakumar]

Thanks for the reply.

No.First Premise/Premise type need to apportioned based on Ratio apportioned to sub-cities.Then it need to be clubbed based on Premise type.

Extremely sorry for wrongly provided input data.

Below is the data and file attached.

Hi ,

I am not sure that this is what you want. See if it is OK.

Narayan

 

[bosco_yip]

Try,

In C14, formula copy across and down :

=SUMPRODUCT(VLOOKUP(T(IF({1},$D$4:$D$9)),$G$5:$K$7,MATCH($B14,$G$4:$K$4,0),0)*($C$4:$C$9=C$13)*$E$4:$E$9)

Regards
Bosco

 

[Nandakumar]

Thanks.It works.

Thank you so much.

Try,

In C14, formula copy across and down :

=SUMPRODUCT(VLOOKUP(T(IF({1},$D$4:$D$9)),$G$5:$K$7,MATCH($B14,$G$4:$K$4,0),0)*($C$4:$C$9=C$13)*$E$4:$E$9)

Regards
Bosco

 

[NARAYANK991]

Hi ,

Another option would be the following array formula , to be entered using CTRL SHIFT ENTER :

=SUM(SUMIFS($E$4:$E$9,$D$4:$D$9,$G$5:$G$7,$C$4:$C$9,C$13) * INDEX($G$5:$K$7,,MATCH($B14,$H$4:$K$4,0)+1))

Enter this in C14 , and copy across and down.

Narayan

 

[Nandakumar]

Hi ,

Another option would be the following array formula , to be entered using CTRL SHIFT ENTER :

=SUM(SUMIFS($E$4:$E$9,$D$4:$D$9,$G$5:$G$7,$C$4:$C$9,C$13) * INDEX($G$5:$K$7,,MATCH($B14,$H$4:$K$4,0)+1))

Enter this in C14 , and copy across and down.

Narayan

Hi,

Its works.what would be the sumproduct formula if,if i interchange the ratio apportionment with rows & columns.Attached below the sheet:

 

[NARAYANK991]

Hi ,

Try this :

=SUMPRODUCT(HLOOKUP(T(IF({1},$D$4:$D$9)),$G$4:$K$8,MATCH($B14,$G$4:$G$8,0),0)*($C$4:$C$9=C$13)*$E$4:$E$9)

Narayan

 

[Nandakumar]

Hi,

Total is not matching.Attached the sheet for your review.

Hi ,

Try this :

=SUMPRODUCT(HLOOKUP(T(IF({1},$D$4:$D$9)),$G$4:$K$8,MATCH($B14,$G$4:$G$8,0),0)*($C$4:$C$9=C$13)*$E$4:$E$9)

Narayan

 

[NARAYANK991]

Hi ,

That depends on how the total is supposed to match.

If the row totals are 100% , then the row totals in the output will be correct.

If the column totals are 100% , then the column totals will be correct.

For both to be correct , both the row totals and the column totals have to equal 100%.

Narayan

 

[Nandakumar]

Hi,

I want row totals to be 100% match i.e.subcity
Hope you understand.

Thanks for the support.

Hi ,

That depends on how the total is supposed to match.

If the row totals are 100% , then the row totals in the output will be correct.

If the column totals are 100% , then the column totals will be correct.

For both to be correct , both the row totals and the column totals have to equal 100%.

Narayan

 

[NARAYANK991]

Hi ,

Please mention what should be the totals for Chennai , Mumbai and Delhi for A1 i.e. what should be the totals of C15 , D15 , E15 ?

Narayan

 

[Nandakumar]

Hi,

I fixed it.Now its working fine.

Thanks for your kind support.

Hi ,

Please mention what should be the totals for Chennai , Mumbai and Delhi for A1 i.e. what should be the totals of C15 , D15 , E15 ?

Narayan