Need help summing data from a specific Month
Hi everyone,
I need assistance summing and counting data from a specific month. For the "Actual" column, I need two formula's in which it sums all the values up until the date I have under the "Date column", I also need it to start looking from the beginning of that exact month under the "Date" column too (Light Blue). I then need a similar formula in which it counts all those value for the same criteria as the light blue highlighted area, where it's from the beginning of that month up until the date in the "Date" column.
Regards
Rainier
Best Answers
-
Ok. Try something like this...
=SUMIFS({Range to Sum}, {Date Column To Evaluate}, IFERROR(MONTH(@cell), 0) = MONTH(Date@row), {Text Range}, @cell = [Column8]@row)
and
=COUNTIFS({Date Column To Evaluate}, IFERROR(MONTH(@cell), 0) = MONTH(Date@row), {Text Range}, @cell = [Column8]@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
What is in Range 9 and what is in your Month column?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Will the three dates in the light blue portion always match?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
Yes they will all match as I used a max(Date) formula which is linked to another sheet - so it always retrieves the max date into the date column that you can see in the screenshot.
So then I want my formula to look at the month that I got from the max date which I already did in the column next to date, and then I want it to sumif all the values for that specific month for tons, or loads etc, and then I need a similar formula for the red area in which I use a countif.
Regards
Rainier
-
Ok. Try something like this...
=SUMIFS({Range to Sum}, {Date Column To Evaluate}, IFERROR(MONTH(@cell), 0) = MONTH(Date@row), {Text Range}, @cell = [Column8]@row)
and
=COUNTIFS({Date Column To Evaluate}, IFERROR(MONTH(@cell), 0) = MONTH(Date@row), {Text Range}, @cell = [Column8]@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
Thank you for you solutions, I am just not sure what you are referring to when you say "{Text Range}"?
Regards
Rainier
-
Hi Paul,
I figured out what the text range is, however all the answers are returning "0" - I think that reference to the "IFERROR" part of the formula?
Not sure what could be wrong there?
=SUMIFS({Daily Ops Report (VR) - Grid Range 1}, {Daily Ops Report (VR) - Grid Range 9}, IFERROR(MONTH(@cell), 0) = MONTH(Date@row), {Daily Ops Report (VR) - Grid Range 9}, @cell = Month@row)
Rainier
-
What is in Range 9 and what is in your Month column?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
Apologies for the very late response, however I was able to get the solution. Thank you for your assistance!
Regards
Rainier
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!