Complex Formula COUNTIFS for Last Month
I have 2 working formulas that I wish to combine.
The first is a count where MSD Data field = Yes - it works and gives me a result of 65
=COUNTIFS([MSD Product]:[MSD Product], ="Yes")
The second is a complex formula with dates (that this community helped me construct genius that they are) which gives me a count in the previous calendar month i.e January - you can see why I needed help !!!
=COUNTIFS([Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))
What I need to achieve, and cannot seem to get to work, is where the MSD Data field = Yes and the Raised On field was last month.
I've tried adding the MSD part to the front then a comma then the raised on etc. I have also tried adding the MSD field to the end and after another AND but doesn't work.
so for example this doesn't work and gives #unparseable error
=COUNTIFS([Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)) AND ([MSD Product]:[MSD Product], ="Yes"))
I have passed my ability to compute this and really hope that you can help.
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
Best Answer
-
Hi @SueinSpain
Hope you are fine, please try the following formula.
=COUNTIFS([MSD Data field]:[MSD Data field], "yes", [Raised On]:[Raised On], MONTH(@cell) = (MONTH(TODAY()) - 1))
the following screenshot is from the sample sheet i create for your case
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"
Answers
-
Hi @SueinSpain
Hope you are fine, please try the following formula.
=COUNTIFS([MSD Data field]:[MSD Data field], "yes", [Raised On]:[Raised On], MONTH(@cell) = (MONTH(TODAY()) - 1))
the following screenshot is from the sample sheet i create for your case
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"
-
You are an absolute genius.
I have used this, adapted this and it works brilliantly thank you
For a new challenge :) I want to do something very similar with =SUMIFS but again cannot get the right number of commas to work so obviously being stupid (again!)
I am trying to add up the [Value of Goods] for last month. I have ...... but get #unparseable
=SUMIFS([Value of Goods]:[ Value of Goods], [Raised on]:[Raised on], MONTH(@cell) = (MONTH(TODAY()) - 1))
Just cannot see where I am going wrong so hope you can help with this one too.
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
-
Hi @SueinSpain
Try something like this.
=SUMIFS([Value of Goods]:[ Value of Goods], [Raised on]:[Raised on], MONTH(@cell) = MONTH(TODAY()) - 1)
This was wrong in yours.
=SUMIFS([Value of Goods]:[ Value of Goods], [Raised on]:[Raised on], MONTH(@cell) = (MONTH(TODAY()) - 1))
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
no still #unparseable
It is in a summary sheet if that makes any difference?
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
-
Do you mean Sheet Summary and not another sheet?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I think the rain is getting to me - yes sorry in the Sheet Summary for a single smartsheet
I previously used your brilliant =COUNTIFS to get me total number of records for previous month (3 months & 12 months - you are a genius)
=COUNTIFS([Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))
Then needed to epand to count if a value in a field is met and record in the last month - got there with help again this time from Bassam!
=COUNTIFS([MSD Product]:[MSD Product], "Yes", [Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))
But now instead of a count I need the sum of a [Value of Goods] field.
Can do the grand total so all field contents OK. just have troubles doing it for the last calendar month. Thinking of deleting users :) so they don't keep asking for these difficult solutions.
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
-
Haha! No worries!
Thanks for the kind words.
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
have shared on the sheet - the correct one 2nd time around :)
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!