Nested Sumproduct formula

[Nandakumar]

Hi,

Can you please help on the sumproduct formula for the below.

Cost centers are in one sheet and Division are on the other sheet.I need to find total sum.


Attached excel sheet fr kind reference.

 

[bosco_yip]

In J3, formula copy down :

=SUMPRODUCT((LOOKUP(B$3:B$8,F$3:F$8,E$3:E$8)=I3)*C$3:C$8)

Regards
Bosco

 

[Nandakumar]

Wow.Thank you.It works!!!

 

[bosco_yip]

In J3, formula copy down :

=SUMPRODUCT((LOOKUP(B$3:B$8,F$3:F$8,E$3:E$8)=I3)*C$3:C$8)

Regards
Bosco

1] Post #.2 formula invoking LOOKUP of it requires sorting Table in ascending order.

2] So if the Lookup range doesn't in sorting order, try to use Sumproduct+Countifs formula instead.

In J3, formula copy down :

=SUMPRODUCT(COUNTIFS(B$3:B$8,F$3:F$8,E$3:E$8,I3),C$3:C$8)

Regards
Bosco