Table in excel

[Saurabh Singh]

Dear Friends,

I have following table:

Table 1
Sl. No.LengthBreadthHeightBy Air By Surface
Box 157514827.91 31.01
Box 241353911.19 12.44
Box 33030254.50 5.00
Box 458414119.50 21.67

Table 2
Sl. No.ModeTotal No. of BoxesBox 1Box 2Box 3Box 4Volumetric Wt.
1Air 4 1 3
2Surface 3 1 1 1

Table 1 is the master having Box's Volumetric weight if sent by Air and Surface. I require that, when i enter Qty. in Table 2 against respective Box Number, it should calculate total of volumetric weight in Table 2, last column.

Thank you in advance ...
Saurabh

 

[PCosta87]

Hi,

Please upload a sample excel file with the data.

Thanks

 

[Saurabh Singh]

Dear PCosta,

Thank you for your prompt response. Please find the sheet attached.

 

[PCosta87]

Dear PCosta,

Thank you for your prompt response. Please find the sheet attached.

Hi,

Here you go... please refer to attachment
You can probably do the same with sumproduct but I couldn't get it to work just yet

Hope this helps

 

[PCosta87]

With sumproduct (refer to attachment)

Cheers

 

[Gireesh]

Hi Saurabh,

Hope, this will help you.

I used the formula below....

=IF($B5="Air",SUM(($D5*'Box Master'!$E$2)+('Main Sheet'!$E5*'Box Master'!$E$3)+('Main Sheet'!$F5*'Box Master'!$E$4)+('Main Sheet'!$G5*'Box Master'!E6)),IF($B5="Surface",SUM(($D5*'Box Master'!$F$2)+('Main Sheet'!$E5*'Box Master'!$F$3)+('Main Sheet'!$F5*'Box Master'!$F$4)+('Main Sheet'!$G5*'Box Master'!$F$5)),"Error Found"))

[ATTACH=full]37976[/ATTACH]

 

[bosco_yip]

Just simplified Pcosta's formula.

In H4, enter non-array formula copy down :

=IF(B4="","",SUMPRODUCT($D4:$G4,SUMIF('Box Master'!$A$2:$A$5,$D$3:$G$3,IF(B4="Air",'Box Master'!$E$2:$E$5,'Box Master'!$F$2:$F$5))))

Regards
Bosco

 

[John Jairo V]

Hi to all!

Could be too (NON-CSE):
=MMULT(--D4:G4,IF(B4="Air",'Box Master'!E$2:E$5,'Box Master'!F$2:F$5))

Or:
=MMULT(--D4:G4,OFFSET('Box Master'!E$2:E$5,,B4<>"Air"))

Or:
=MMULT(--D4:G4,INDEX('Box Master'!E$2:F$5,,1+(B4<>"Air")))

Blessings!

 

[Saurabh Singh]

Dear All,

Thank you very much for the support and help.
This worked well and i can move ahead now.
Thank you PCosta, Gireesh, Bosco_Yip & John Jairo V
Cheers

 

[Saurabh Singh]

Dear Friends,

Please find the attachment. It is to achieve same objective but without depending upon a master/ reference sheet. It will calculate values from the header above.

 

[bosco_yip]

Dear Friends,

Please find the attachment. It is to achieve same objective but without depending upon a master/ reference sheet. It will calculate values from the header above.

K4 :

=SUMPRODUCT(C$3:J$3*C4:J4)

Regards
Bosco