Mutliplying mutiple columns + multiplying mutiple columns
Smartsheet does not like multiplying 0's so I need some help.
I can get around this by adding more columns in but that will be painful with 13 similar sheets! lol There has to be a way to do this without additional columns.
There are times when the MU columns are all 0, but there are REP gallons used. I get an "invalid operation" only when there are 0s involved. I still need it to multiply though.
=IF([1310 MU Gallons Used]@row = 0, "", ([1310 MU lbs per gallon]@row * [1310 MU Gallons Used]@row * [1310 MU Price per lbs ($)]@row)) + IF([1310 REP Gallons Used]@row = 0, "", ([1310 REP lbs per gallon]@row * [1310 REP Gallons Used]@row * [1310REP Price per lbs ($)]@row))
Answers
-
Is there a specific reason you need the IF() statement? It seems you could just multiply each group of three columns and sum the total.
=([1310 MU lbs per gallon]@row * [1310 MU Gallons Used]@row * [1310 MU Price per lbs ($)]@row) + ([1310 REP lbs per gallon]@row * [1310 REP Gallons Used]@row * [1310REP Price per lbs ($)]@row)
-
I get an invalid argument
-
That is very strange. I have it working in a sample sheet. If you copy/pasted the formula, you may want to double check that you didn't miss a character at the end. Beyond that, I'm not really sure what would cause that error.
-
is that functioning when there are 0 gallons used?
-
Yes, it is setup as a column formula in my screenshot, so the same formula is being applied to each row.
-
I am clicking in the cell@row so there are no typo issues. I don't know why this won't work.
-
This should work, so there is definitely something unexpected with the cells involved. In an attempt to troubleshoot, you could try splitting up the formula to see if only certain cells/columns are causing the problem. I would start by breaking the formula in half.
=([1310 MU lbs per gallon]@row * [1310 MU Gallons Used]@row * [1310 MU Price per lbs ($)]@row)
=([1310 REP lbs per gallon]@row * [1310 REP Gallons Used]@row * [1310REP Price per lbs ($)]@row)
If only one of those formulas causes a problem, you can break it down further to only multiply two column at a time to narrow it down even more.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!