Generating Weekly / Monthly / Quarterly Stats

Hi,

I have a pretty basic project tracker and I need to create some graphs from it.

The graphs need to be derived for weekly, monthly, quarterly delivered projects. I created a mock up for the monthly stats and would appreciate any help to arrive at these values based on the following logic:

Projects delivered in June = C and D, Value generated = 1027+500=1527 this number needs to be updated alongside June by using a formula that references another sheet.

Thanks in advance. I am sure this is a problem someone has already solved and I am having a hard time figuring out formula + sheet referencing since I am new to Smartsheet.

Regards

Diya

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are actually closer with your first one.


    =SUMIFS(range_to_sum, 1st_criteria_range, 1st_criteria, 2nd_criteria_range, 2nd_criteria)


    Notice how you first tell the formula WHAT you want to sum. Then you move on to repeat the pattern of "range comma criteria comma range comma criteria".


    Following that syntax, all you are missing is a comma between your range and criteria.

    =SUMIFS({Project Intake Sheet Forecast BV}, {Project Intake Sheet Project Status}, "Complete", {Project Intake Sheet End Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2020))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To get the delivered value in June 2020, it would look something along the lines of...

    =SUMIFS({Other Sheet Value Column}, {Other Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2020))

  • Thanks Paul. I tried it out but I am having a hard time cracking sheet referencing. The name of the sheet I am trying to reference is called 'Project Intake Sheet' and the Primary Column is 'Project ID'. Here is what I got, but I am certain I am off:

    =SUMIFS({Project ID}, {Value}, {End Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2020))

    where,

    'Project ID' is the primary column in the sheet 'Project Intake Sheet'

    'Value' & 'End Date' are the columns of interest as mentioned previously.

    I get an error 'Invalid Ref'

    It would be great if you could point me to some articles on the syntax for cross referencing. Thanks much for your time!

  • This is super helpful. I took a stab at it (also added a criteria of Project Status=Complete. The help suggests range and then criteria so I am confused on how that would be structured. Sorry about the back and forth, but I am new to this!

    =SUMIFS({Project Intake Sheet Forecast BV}, {Project Intake Sheet Project Status} = "Complete", {Project Intake Sheet End Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2020))

    OR

    =SUMIFS({Project Intake Sheet Forecast BV}, {Forecast BV}, {Project Intake Sheet Project Status}, {Project Status} = "Complete", {Project Intake Sheet End Date}, {End Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2020))

    Here Forecast BV is 'Value'.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are actually closer with your first one.


    =SUMIFS(range_to_sum, 1st_criteria_range, 1st_criteria, 2nd_criteria_range, 2nd_criteria)


    Notice how you first tell the formula WHAT you want to sum. Then you move on to repeat the pattern of "range comma criteria comma range comma criteria".


    Following that syntax, all you are missing is a comma between your range and criteria.

    =SUMIFS({Project Intake Sheet Forecast BV}, {Project Intake Sheet Project Status}, "Complete", {Project Intake Sheet End Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2020))

  • This worked! Thank you Paul, appreciate your help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️


    Please don't forget to mark the most appropriate response(s) as helpful. That way other people searching for a similar solution can know that one may be found here.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!