Generating Weekly / Monthly / Quarterly Stats

dpaul89096

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

Paul Newcome

Start typing your formula:

=SUMIFS(

In the helper box that pops up below the cell you are working in, click on the hyperlink that says "Reference Another Sheet". Select the sheet you want to reference from the list on the left then select the range you want to reference from that sheet. To reference an entire column, click on the column header. Then click the blue "Insert Reference" button in the bottom right corner.

You will now be taken back to the sheet you are working in and should see

=SUMIFS({Other Sheet Name Range 1}

Just continue typing your formula, and whenever you get to where you need a cross sheet reference, follow the above steps.

For the above, you should only have two ranges.

Range 1 = Value column from the other sheet

Range 2 = Date column from the other sheet

Paul Newcome

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))

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))

dpaul89096

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!

Paul Newcome

Start typing your formula:

=SUMIFS(

In the helper box that pops up below the cell you are working in, click on the hyperlink that says "Reference Another Sheet". Select the sheet you want to reference from the list on the left then select the range you want to reference from that sheet. To reference an entire column, click on the column header. Then click the blue "Insert Reference" button in the bottom right corner.

You will now be taken back to the sheet you are working in and should see

=SUMIFS({Other Sheet Name Range 1}

Just continue typing your formula, and whenever you get to where you need a cross sheet reference, follow the above steps.

For the above, you should only have two ranges.

Range 1 = Value column from the other sheet

Range 2 = Date column from the other sheet

dpaul89096

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

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))

dpaul89096

This worked! Thank you Paul, appreciate your help!

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.