"kittronald" wrote:
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 ?

Perhaps this explanation from the SUMIF help page is sufficient (emphasis
added):

"The cells in sum_range are summed only if their __corresponding__ cells in
range match the criteria".

Since sum_range and the first range must have "corresponding cells", that
implies that both ranges must be the same size. The #VALUE error arises
because they do not in C8 and C9.

kittronald wrote:
B6 is =INDEX($1:$1048576,2,2):INDEX($1:$1048576,4,2)
C6 is =INDEX($1:$1048576,2,3):INDEX($1:$1048576,4,3)
[....]
Is there a way to calculate formulas like C6 once and
then refer to them as the SUMIF sum_range ?

The formulas in B6 and C6 are nonsense, which is why they return #VALUE
unless you enter them as array formulas. But even as array formulas, they
might no sense to me.

kittronald wrote:
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

We are not mindreaders. It would be helpful if you had filled in columns B
and C in rows 8 and 9 so that we knew exactly what you wanted. Perhaps the
following:

B8: =SUMIF($A$2:$A$4,A8,$B$2:$B$4)
C8: =SUMIF($A$2:$A$4,A8,$C$2:$C$4)

Copy B8:C8 into B9:C9.

kittronald wrote:
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

Non sequitur! What "duplicate" calculations? If you mean the "duplicate"
comparison of A8 with A2:A4, the only way I can think of to avoid that is
use VBA user-defined function in an multicell array formula, namely B8:C8.
But that might not be faster than the two SUMIFs (per fruit).