Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Comparing Targets and Actual's

Andrew Currie
edited 12/09/19 in Archived 2016 Posts

I have a data set where the targets are set at the start of the year and as the year progresses actuals are loaded (through Webform) which appear on a different row. I am trying to calculate the performance each month (Actuals / Target) but I dont know how to calculate this when the values are on different rows. I'm sure there is an easy workaround to join the data according to the month / Year column

Jan-15 20
Feb-15 30
Mar-15 40
Apr-15 50
May-15 60
Jun-15 70


thanks in anticipation


  • Jim Hook
    Jim Hook ✭✭✭✭✭✭
    edited 06/01/16

    Hi Andrew,


    I don't have the time now to fully investigate your issue but I do similar processing using the SUMIFS() function. The function would look for values where the month matches the current month being summed and the year matches the current year being summed. It would only sum values that match both the month and year. The only other issue I see is that the data has to be in a row range that includes all the values being summed and I'm not sure how that would work if new rows are always coming in via Webforms.

  • Kate French

    Hi Andrew,


    To build on what Jim suggested, you might check out using a range in your formulas. As long as the formulas aren't built in the same column they're summing, you can have them look for an entire column range with the syntax [Column Name]:[Column Name] rather than [Column Name]1:[Column Name]10. That way new rows added by web forms will be caught in your totals. 


    Check out more information on formulas here: http://help.smartsheet.com/customer/portal/articles/775363

This discussion has been closed.