Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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
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).


  #3   Report Post  
Old 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
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to update a column in a work based on another work sheet WickerMan New Users to Excel 1 December 4th 09 12:58 PM
how can i automatically generate work order numbers from work orde rob h Excel Discussion (Misc queries) 1 July 13th 09 07:59 PM
flash object dont work in my excel work sheet Nitn Excel Discussion (Misc queries) 0 July 4th 09 08:00 AM
Counting dates in multiple work sheets and work books Savage Excel Discussion (Misc queries) 0 December 19th 05 11:41 PM
Is there away to keep "auto save" from jumping to the first work sheet in the work book? Marc New Users to Excel 2 April 21st 05 01:27 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017