SUMIFS not working correctly

jb@59069 ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I have a base formula with variations to calculate expenses based on date range. Today I discovered that one variation is not working correctly, summing Personal Car Mileage cost for reimbursement. Below is the formula to calculate total amount from Oct 1 to Oct 15.

=SUMIFS(Amount:Amount, [Date Charged]:[Date Charged], @cell > DATE(2018, 9, 30), [Date Charged]:[Date Charged], @cell < DATE(2018, 10, 16), [Deliverable/Project]:[Deliverable/Project], ="Event – ACG", [Charge Description]:[Charge Description], ="Trans (personal mileage, rental car)")

There is a column, Personal Car Mileage, that is filled via a form in the sheet, where # of miles is entered. In the Amount column, the PCM entry is multiplied by the 2018 IRS rate of 0.545.

All other variations of this formula seem to be totaling the Amount column correctly, but the entries for PCM are not.

There are two (2) entries for this time period, 10/5 and 10/10, each for 89 miles, giving a $48.51 in each cell. However, this formula is only showing $48.51 total rather than $97.02 as it should.

Any ideas? Is it because the Amount cell(s) for PCM are based on a formula?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!