Sum alternating cells linked to other workbook

[Kimber]

I have a worksheet that is populated from another workbook and has two total columns. There are two columns for each period -- quantity and dollars -- for each period, so the total columns correspond to the appropriate column periods. The sum formulas work if data is in the same workbook, but I get an error message (#VALUE!) if linked to another workbook. I've tried formulas using SUMIF and SUMPRODUCT with ISNUMBER unsuccessfully. Sample attached.

 

[NARAYANK991]

Hi ,

I am not sure I have understood you , but try these two array formulae , to be entered using CTRL SHIFT ENTER.

=SUM(N(OFFSET($A$3,,2*(COLUMN($A$3:$L$3)-MIN(COLUMN($A$3:$L$3))))))

=SUM(N(OFFSET($B$3,,2*(COLUMN($B$3:$L$3)-MIN(COLUMN($B$3:$L$3))))))

Narayan

 

[bosco_yip]

1] In Y3,

=SUMIF($A$2:$X$2,"*Qty",$A3:$X3)

2] In Z3,

=SUMIF($A$2:$X$2,"*$",$A3:$X3)

Regards
Bosco

 

[Kimber]

Hi ,

I am not sure I have understood you , but try these two array formulae , to be entered using CTRL SHIFT ENTER.

=SUM(N(OFFSET($A$3,,2*(COLUMN($A$3:$L$3)-MIN(COLUMN($A$3:$L$3))))))

=SUM(N(OFFSET($B$3,,2*(COLUMN($B$3:$L$3)-MIN(COLUMN($B$3:$L$3))))))

Narayan

Thank you, Narayan. I was not able to get this to work for me on this sheet. However, Bosco's solution was successful. Thank you so much for taking time to respond to my request for help.

 

[Kimber]

1] In Y3,

=SUMIF($A$2:$X$2,"*Qty",$A3:$X3)

2] In Z3,

=SUMIF($A$2:$X$2,"*$",$A3:$X3)

Regards
Bosco

Bosco, thank you so much for your prompt reply. Your solution worked perfectly in my worksheet.