Using formula in a Date column, then using SUMIF to total


Hi all,

Relatively new to Smartsheets and I am looking for a bit of guidance here.

I am trying to put together a 'Resource Forecasting Tool' for our company, wherein the users can populate either an 'Personnel Discipline' or 'Equipment Item' to forecast, they then will enter the start and end date and the quantity required. 

Sheet Names are:

- Forecast Sheet;

- Summary Sheet.

The Forecast Sheet currently then uses the below formula to work out if the start and end dates fall within the week number and populate the cell with the quantity required is conditions are met:

=IF(OR($[Start Date]3 = [W1]$1, $[Start Date]3 > [W1]$1), $Qty3, IF($[End Date]3 > [W2]$1, $Qty3, IF(AND($[End Date]3 > [W1]$1, $[End Date]3 < [W2]$1), $Qty3, ""))) + ""

Now, as you will see I am having to use the ' + "" ' function to allow a formula to be used within a date formatted column. I had to do this, as I could not use a formula to compare the dates within a text formatted column. 

So, the formula works perfectly and all is going great, however now I want a formula on Summary Sheet to SUMIF all required items based on a pre-defined list of resources. For example, if in Forecacst Sheet the user wants to forecast 5 x technicians in Week 1, I need Summary Sheet to check use the below SUMIF to find out how many Technicians have been forecasted in Week 1.

=(SUMIFS({W1 SUM}, {W1 SUM}, >0, {DISCIPLINE}, $Resource3, {DISCIPLINE}, @cell = $Resource3))

Here is the problem, the above formula does not calculate anything due to the fact that all of the data being output from the Forecast Sheet is coming from Date formatted columns. Even if I add the ' + "" ' to the end of the formula it only responds with 0.

Anybody got any suggestions on how I can adapt this to work? 

TL;DR - How do I get formula driven data from a Date formatted column?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!