K

#### kittronald

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.