Why am I getting #VALUE! error?

%3CLINGO-SUB%20id%3D%22lingo-sub-1389719%22%20slang%3D%22en-US%22%3EWhy%20am%20I%20getting%20%23VALUE!%20error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1389719%22%20slang%3D%22en-US%22%3E%3CP%3E%3DSUM('2020%20Schedule'!FV2%3AFV3%2C'2020%20Schedule'!FV7%3AFV10*2%2C'2020%20Schedule'!FV19%3AFV20*3%2C'2020%20Schedule'!FV25%3AFV32)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20formula%20for%20an%20individual%20part%20needed%20to%20build%20specific%20finished%20machines%20-%20so%20for%20models%20in%20FV7%20through%20FV10%20I%20need%20two%20of%20each%20part%20for%20the%20build%2C%20FV19%20and%20FV20%20I%20need%20three%20of%20each%20part...%20I%20don't%20understand%20why%20I%20keep%20getting%20the%20%23VALUE!%20error%20-%20it%20works%20fine%20and%20gives%20me%20the%20correct%20total%20in%20the%20'sum%20formula%20wizard'%20but%20when%20I%20hit%20enter%2C%20I%20get%20the%20error%20in%20the%20cell.%26nbsp%3B%20Thoughts%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1389719%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1389841%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20am%20I%20getting%20%23VALUE!%20error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1389841%22%20slang%3D%22en-US%22%3ESorry%20if%20this%20is%20obvious%20or%20if%20you've%20already%20tried%20this.%20I%20ran%20into%20a%20similar%20problem%20and%20it%20turned%20out%20I%20had%20a%20random%20space%20in%20my%20formula%2C%20might%20be%20a%20place%20to%20start.%3C%2FLINGO-BODY%3E
New Contributor

=SUM('2020 Schedule'!FV2:FV3,'2020 Schedule'!FV7:FV10*2,'2020 Schedule'!FV19:FV20*3,'2020 Schedule'!FV25:FV32)

 

This is my formula for an individual part needed to build specific finished machines - so for models in FV7 through FV10 I need two of each part for the build, FV19 and FV20 I need three of each part... I don't understand why I keep getting the #VALUE! error - it works fine and gives me the correct total in the 'sum formula wizard' but when I hit enter, I get the error in the cell.  Thoughts?

3 Replies
Highlighted
Sorry if this is obvious or if you've already tried this. I ran into a similar problem and it turned out I had a random space in my formula, might be a place to start.
Highlighted

@OliviaMullen  I think you have a couple of problems.  The basic problem is that you are trying to use multiplication inside the SUM function.  The easiest solution in your case is to manually do the *2 and *3 as follows:

=SUM('2020 Schedule'!FV2:FV3,'2020 Schedule'!FV7:FV10,'2020 Schedule'!FV7:FV10,'2020 Schedule'!FV19:FV20,'2020 Schedule'!FV19:FV20,'2020 Schedule'!FV19:FV20,'2020 Schedule'!FV25:FV32)

 

if you wanted higher level products or the such you could use SUMPRODUCT to do the array calculation, but the next problem is that your arrays are of different size and that won't work.

So lastly you could use a helper column and do the product/selection and then just get the sum from that column.  

 

EDIT: just realized, I bet it used to work and doesn't now?  You can use CTRL+SHIFT+ENTER to enter it as an array formula and then it will work

Highlighted

@OliviaMullenI figured it out - I don't know if it's the most eloquent, but it works:

 

=SUM('2020 Schedule'!FV2:FV3,(SUM('2020 Schedule'!FV7:FV10)*2),(SUM('2020 Schedule'!FV19:FV20)*3),'2020 Schedule'!FV25:FV32)

 

Formulas within the formula I guess?