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)
-
What is in Range 9 and what is in your Month column?
Answers
-
Will the three dates in the light blue portion always match?
-
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)
-
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?
-
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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!