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!