Reducing repetitive calculation not working

[kittronald]

A B C
1 Fruit Qty =INDEX($1:$1048576,2,2):INDEX($1:$1048576,4,2)
2 Apple 1
3 Orange 2
4 Apple 3

Why does =SUMIF(A2:A4,"Apple",B2:B4) return the correct value ...


.... while =SUMIF(A2:A4,"Apple",C1) returns a #VALUE error ?

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

I tried creating a name with the formula in C1 and using the name in
the SUMIF formula as the sum_range and that works.

But since the sum_range changes, I would have to use the OFFSET
function to increment and that would add too much calculation
volatility.

Is there a way to calculate C1 once and then refer to it as the SUMIF
sum_range ?



- Ronald K.

 

[GS]

kittronald used his keyboard to write :

A B C
1 Fruit Qty =INDEX($1:$1048576,2,2):INDEX($1:$1048576,4,2)
2 Apple 1
3 Orange 2
4 Apple 3

Why does =SUMIF(A2:A4,"Apple",B2:B4) return the correct value ...


... while =SUMIF(A2:A4,"Apple",C1) returns a #VALUE error ?

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

I tried creating a name with the formula in C1 and using the name in
the SUMIF formula as the sum_range and that works.

But since the sum_range changes, I would have to use the OFFSET
function to increment and that would add too much calculation
volatility.

Is there a way to calculate C1 once and then refer to it as the SUMIF
sum_range ?



- Ronald K.

You're trying to do subtotals (summary values) in a detailed list. If
you want to reduce the number of calcs you could put your sums in a
separate table where each item is listed only once (ie: unique values).
Otherwise, a pivot table might be a better alternative.