- **Excel Worksheet Functions**
(*https://www.excelbanter.com/excel-worksheet-functions/*)

- - **Why doesn't this work ?**
(*https://www.excelbanter.com/excel-worksheet-functions/270864-why-doesnt-work.html*)

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

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

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

All times are GMT +1. The time now is 10:13 PM. |

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.

ExcelBanter.com