A B C
1 Fruit Qty1 Qty2
2 Apple 1 2
3 Orange 2 4
4 Apple 1 6
5
6
7 Fruit Qty1Total Qty2Total
8 Apple
9 Orange


Where ...

B6 is =INDEX($1:$1048576,2,2):INDEX($1:$1048576,4,2)

C6 is =INDEX($1:$1048576,2,3):INDEX($1:$1048576,4,3)

B8 is =SUMIF($A$2:$A$4,"Apple",$B$2:$B$4)

C8 is =SUMIF($A$2:$A$4,"Apple",$C$6)

B9 is =SUMIF($A$2:$A$4,"Orange",$B$2:$B$4)

C9 is =SUMIF($A$2:$A$4,"Orange",$C$6)



Why do the B column formulas, =SUMIF($A$2:$A$4,"Apple",$B$2:$B$4),
return the correct value ...


.... while the C column formulas, =SUMIF($A$2:$A$4,"Apple",$C$8),
return a #VALUE error ?


The reason I'm not including the C6 formula in the B column formulas
as the sum_range in each SUMIF formula is to reduce the number of
duplicate calculations (100,000+).

Creating names for formulas like C6 works, but on row eight, there
would be hundreds of these formulas requiring an equal amount of
names.

Is there a way to calculate formulas like C6 once and then refer to
them as the SUMIF
sum_range ?


- Ronald K.