Reporting and Function/Formula Questions

Brandy Milam

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.

 

 

Ex: 

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

 

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

 

Ex: 

=[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

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

I like it.

 

Craig

Brandy Milam

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

 

Brandy Milam

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

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