Averaging numbers once they have been filtered.

Coen

Good morning all,

I am working on a report for multiple locations.

I'm currently stuck trying to figure out how to create an average for counts collected between two dates. (Weekly/Monthly)

I've created a filter that will allow me to select the dates I want to look at, but my total averages do not change when I filter my results down to 5 from 20. I still see averages and counts for all data filtered and unfiltered.

Thank you!

-Rob

Jeff Reisman

@Coen

You need a formula to do this. A filter just filters the view presented to the user, it doesn't affect calculations in the sheet. You could do this with either some helper columns to enter your start and end dates, or use a helper sheet for the same thing.

Since the AVERAGEIF formula only lets you use a single criteria, we will need to use COUNTIFS and SUMIFS to do the calculations.

I'm going to call your columns "CollectedDate", "StartDate", "EndDate", and "Amount".

=SUMIFS(Amount:Amount, CollectedDate:CollectedDate, >= StartDate@row, CollectedDate:CollectedDate, <= EndDate@row) / COUNTIFS(CollectedDate:CollectedDate, >= StartDate@row, CollectedDate:CollectedDate, <= EndDate@row)

In English - Add up the values in the Amount column where the CollectedDate is on or after the StartDate on this row and is on or before the EndDate on this row, and then divide that by the count of rows where the CollectedDate is on or after the StartDate on this row and is on or before the EndDate on this row.

Jeff Reisman

@Coen

You need a formula to do this. A filter just filters the view presented to the user, it doesn't affect calculations in the sheet. You could do this with either some helper columns to enter your start and end dates, or use a helper sheet for the same thing.

Since the AVERAGEIF formula only lets you use a single criteria, we will need to use COUNTIFS and SUMIFS to do the calculations.

I'm going to call your columns "CollectedDate", "StartDate", "EndDate", and "Amount".

=SUMIFS(Amount:Amount, CollectedDate:CollectedDate, >= StartDate@row, CollectedDate:CollectedDate, <= EndDate@row) / COUNTIFS(CollectedDate:CollectedDate, >= StartDate@row, CollectedDate:CollectedDate, <= EndDate@row)

In English - Add up the values in the Amount column where the CollectedDate is on or after the StartDate on this row and is on or before the EndDate on this row, and then divide that by the count of rows where the CollectedDate is on or after the StartDate on this row and is on or before the EndDate on this row.

Coen

Hi Jeff! and thank you.

right now, I am using a filter to select my before and after dates. would I be able to manipulate what you have done above to work with that?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/330320#Comment_330320

No. As I said, you can't perform calculations based on filter selections. If you want to be able to select start/end dates and find values based on selected rows, this has to be done via a formula.

Coen

Thanks Jeff,

I was finally able to get this functioning. I'm pretty sure using this formula i can figure out a few other things I'd like to measure.

Much appreciated!