Why does the formula work on the cells i originally used it for, and not the new cells?

So, I've had this sheet working since last summer. Basically it's an inventory and project management tracker for fiber network construction. Tracks the units used in the field, tallies them up, categorized by date, and does the math to show cost.

The formula I use works just fine on nearly everything, it's a pretty simple SUMIFS formula. Count the unit total from another sheet based on date and unit type criteria. But I've recently added some new units, copy/pasted the formula, and now it doesn't work. I don't receive any errors, I just get zero's as if it's not recognizing the inputs from the referenced sheet.

Formula in use: =SUMIFS({C&C FOOTAGE}, {C&C DATE}, <=$[SELECT DATE (END/TODAY)]$3, {C&C UNIT}, [RUS UNIT]@row)

Basically if the date of the unit being installed is less than or equal to today's date, count the footage for all units of the same name as the unit in question. Works fine in every other instance, and I use a very similar formula in another sheet that categorizes by individual month. Everything works there too, except for these same units.

Names of units where the formula doesn't work: SEBO2, SEBO2(36), SEBO2(48), SEBO(B), SEBO(D), SEBO(I)

I have many other units that follow similar nomenclature using parenthesis, dashes, numbers, letters... Nothing has ever seemed to cause a problem like this before. And yes, the units in the reference sheet match the units in the sheet in question exactly. I've tripled checked lol.

Any idea why this might be happening? It is incredibly frustrating having the formula work perfectly on 25+ cells, but for these 6 cells it just decides it doesn't work.

Best Answer

  • Austinc427
    Answer ✓

    HAHAHA I'm a moron. NVM. I realized what my mistake was. I never updated the Date Selection cell in my totals sheet, so my new unit inputs were being ignored because they weren't within the selected date range.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!