Using SUMIF with criteria to track weekly cashflow

Hi all,

Basically, I'm looking for a way to track cashflow so that our Spending from week to week never outweighs the Invoiced AMT that we've collected up un. I have very little experience with formulas, so I've scoured the forums but haven't had any luck.

I currently have a project spreadsheet called Schedule - Flares that contains a Start Date, End Date, and Invoiced AMT and Spending associated with each item (below). I also use a formula in the "Week Of" column to give me the Monday of the start date (basically getting around the WEEKNUMBER function because it's more intuitive to me).

On another sheet, Cashflow - Flares, I would like to use the SUMIFS function to add up the Invoiced AMT from all of the rows where Week Of = a given date.

Range 3 = Invoiced AMT

Range 1 = Week Of

The closest I got was =SUMIFS({Schedule - Flares Range 3}, {Schedule - Flares Range 1}, "8/15/22"). Which returned a value of 0, I assume because my Week Of column is a formula so it's not able to locate "8/15/22".

My goal format is:

Sorry for the lengthy post, I hope this explains well enough! Please let me know if my approach is all wrong.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!