Mutliplying mutiple columns + multiplying mutiple columns

Elizabeth Aird

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

Carson Penticuff

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)

Elizabeth Aird

I get an invalid argument

Carson Penticuff

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.

Elizabeth Aird

is that functioning when there are 0 gallons used?

Carson Penticuff

Yes, it is setup as a column formula in my screenshot, so the same formula is being applied to each row.

Elizabeth Aird

I am clicking in the cell@row so there are no typo issues. I don't know why this won't work.

Carson Penticuff

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.