Remember Me?

#1
July 15th 11, 05:50 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 162
Why doesn't this work ?

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.

#2
July 16th 11, 08:13 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 829
Why doesn't this work ?

"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

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

#3
July 16th 11, 06:55 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 162
Why doesn't this work ?

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.

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post WickerMan New Users to Excel 1 December 4th 09 12:58 PM rob h Excel Discussion (Misc queries) 1 July 13th 09 07:59 PM Nitn Excel Discussion (Misc queries) 0 July 4th 09 08:00 AM Savage Excel Discussion (Misc queries) 0 December 19th 05 11:41 PM Marc New Users to Excel 2 April 21st 05 01:27 AM

All times are GMT +1. The time now is 09:58 AM.