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.

Reporting and Function/Formula Questions

Brandy Milam
edited 12/09/19 in Archived 2015 Posts

Scenario: I created a web form that generates rows in a sheet that require follow-up from another individual. Each line is assigned to someone else for follow up when the web form submitter selects them from a drop down.


That person is then notified. The selected person then goes into the sheet and makes updates to the sheet itself, changing the status as needed. 


I am trying to determine the percentage of the entire document, by it's status AND its assigned person, AND by date range and generate a report.


So far, I have been able to do get the first two parts by using COUNTIFS function.




=COUNTIFS(Status:Status, "Final Attempt Completed", [Assigned Salesperson]:[Assigned Salesperson], "James")


Then I show that number as a percentage in another column. 



=[James Final Attempt Completed]1 / [Total Records]1


Where I am running into trouble is when I try to cross reference this formula in a report with a date range.


I wondered if I could combine these two functions into one column by nesting the functions? Can you nest multiple functions? Can anyone help?


Thanks so much!







  • Travis
    Travis Employee

    Hello! This is all possible and I made a quick sheet to give you an example.


    Here's the sheet: https://app.smartsheet.com/b/publish?EQBCT=060af6dcd3994585bbdd29205f197636


    You cannot compare dates in a COUNTIFS function. Because of this, I added a Checkbox column to a sheet which will check the box if the corresponding Date cell is within the given range. Here's the formula:


    =IF(AND(Date4 >= DATE(2015, 12, 1), Date4 <= DATE(2015, 12, 8)), 1


    I used >= and <= rather than < and > so it includes the dates I added to the range.


    Next, I included the Checkbox column in my COUNTIFS (purple cell in my example):


    =COUNTIFS(Status:Status, "Final Attempt Completed", [Assigned Salesperson]:[Assigned Salesperson], "James", CB:CB, 1)


    Finally, I combined this with a COUNT if the number of records to get a percent (blue cell):


    =(COUNTIFS(Status:Status, "Final Attempt Completed", [Assigned Salesperson]:[Assigned Salesperson], "James", CB:CB, 1)) / COUNT([Assigned Salesperson]:[Assigned Salesperson])


    In this example I counted the number of records in the Assigned Salesperson column but if you have a cell which totals the number of records, [Total Records]1, you can replace COUNT([Assigned Salesperson]:[Assigned Salesperson]) with [Total Records]1.


    Let me know if you have any questions on this! 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I like it.



  • Awesome! I got what I needed. Thank you so much!!


  • I did have one quick question. How do I ensure that the date range formula for the checkbox will auto populate for the entire column?


    I drag/dropped the current formula {=IF(AND([Created Date]1 >= DATE(2015, 11, 1), [Created Date]1 <= DATE(2015, 11, 31)), 1} but I will have more entries coming into the document from the web form. 



  • Travis
    Travis Employee
    edited 12/03/15

    Good question, Brandy! Smartsheet has an autofill formula feature which will automatically add a formula when a new row is added if the same formula is located two rows above or below the newly added row (such as a web form entry).


    Here's more information on this: http://help.smartsheet.com/customer/en/portal/articles/1641473-auto-filling-formulas-and-formatting

This discussion has been closed.