Why doesn't this work ?

[kittronald]

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.

 

[joeu2004]

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.

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.

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.

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).

 

[kittronald]

Joe,

Thanks for the reply.

What I'm trying to do in B6 and C6 is create a dynamic range where
the row and column would be calculated with the MATCH function (not
shown above due to brevity).

Using Evaluate Formula, you can see the formulas in B6 and C6
create a range.

Performing this redundant calculation only once per column would
save a bit of calculation time.

If I create names with the formulas in B6 and C6, the SUMIF
formulas work.

I'm just trying to remove redundant calculations.

However, short of creating a table or PivotTable for the whole
worksheet, including the formulas in B6 and C6 as the sum_range in the
SUMIF formulas may be the only other way to get the desired results.


- Ronald K.