Average if date falls in a month and contains certain text
Still learning average formulas! Trying to come up with a formula to average the number of items where the despatch date is in a certain month (ie January - not of a particular year but all January's) and if another column contains certain text. So it will average the number of items in January if the country column is Asia/Pacific.
Best Answer
-
You are first going to need to insert a text/number column (called "Year" in this example) into the sheet containing the source data and use this column formula:
=IFERROR(YEAR([Date Column Name]@row), "")
Then in the sheet where you want your averages, we would use this:
=IFERROR(COUNTIFS({Source Sheet Date Column}, IFERROR(MONTH(@cell), 0) = 1) / COUNT(DISTINCT(COLLECT({Source Sheet Year Column}, {Source Sheet Date Column}, IFERROR(MONTH(@cell), 0) = 1))), 0)
The above will work for January. For other months, simply change both 1s to the appropriate month number you are wanting to get the average for.
Answers
-
Hi @B Young,
Here's a step-by-step solution for your problem:
1- Create Helper Column "Month" and use column format formula to calculate the month for each row as following:
=MONTH([Dispatch Date]@row)
2- to calculate the Average using your criteria (average the number of items in January if the country column is Asia/Pacific.) use the following formula :
=SUMIFS(Items:Items, [Dispatch Date]:[Dispatch Date], MONTH(@cell) = 1, Country:Country, @cell = "Asia/Pacific") / COUNTIFS(Country:Country, @cell = "Asia/Pacific", Month:Month, 1)
the following is a screen shot for the sample :
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thank you for your reply, I should have mentioned that I'm referencing another sheet, and what I didn't make clear was the number of items is really the number of rows. There is one item per row. Can you help further on that?
-
So you want an average number of rows for January across all years, so that if you had 20 rows in January 2024 and 30 rows in January 2023, the output would be 25?
-
Yes that's correct - but also only wanted to average them if the country column contains Asia/Pacific.
-
How many different years will you have in the source sheet?
-
Earliest year is 2019 and it has continued since then, so there are 6 years so far.
-
You are first going to need to insert a text/number column (called "Year" in this example) into the sheet containing the source data and use this column formula:
=IFERROR(YEAR([Date Column Name]@row), "")
Then in the sheet where you want your averages, we would use this:
=IFERROR(COUNTIFS({Source Sheet Date Column}, IFERROR(MONTH(@cell), 0) = 1) / COUNT(DISTINCT(COLLECT({Source Sheet Year Column}, {Source Sheet Date Column}, IFERROR(MONTH(@cell), 0) = 1))), 0)
The above will work for January. For other months, simply change both 1s to the appropriate month number you are wanting to get the average for.
-
Thank you Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!