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

edited 12/09/19

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!

• Employee

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

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!

• ✭✭✭✭✭✭

I like it.

Craig

• 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.

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