COUNTIF or COUNTIFS - Count specific criteria in the current month
I'm trying to count all the P2's that occurred in the current month without having to change my formula every month. I have to change the "05" for May to the corresponding month each month. Here is my current formula:
=COUNTIFS({2020 from Ethica Date}, LEFT(@cell, 2) = "05", {Priority}, "P2")
I use the same formula for the previous month and change the "05" to "04" so I can capture April. There are multiple priorities so it is time consuming and I really want this somewhat automated.
I use this formula on another page that is similar but I don't need to break it down by priority:
=COUNTIF({Month To}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
I feel like I have searched all over this forum looking for the answer and I'm certain it's out there, I just can't figure it out. Please help!!
Thanks,
Sheryl Roland
Best Answers
-
My apologies.
Current Month:
=COUNTIFS({2020 from Ethica Date}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Priority}, "P2")
Previous Month (accounts for Dec when current month is Jan):
=COUNTIFS({2020 from Ethica Date}, IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), {Priority}, "P2")
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!
-
The problem with my initial formulas is that I had initially misread your ranges. Sorry about that.
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
-
Current Month:
=COUNTIFS({2020 from Ethica Date}, LEFT(@cell, 2) = "05", {Priority}, "P2", {Month To}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
Previous Month (accounts for Dec when current month is Jan):
=COUNTIFS({2020 from Ethica Date}, LEFT(@cell, 2) = "05", {Priority}, "P2", {Month To}, IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1))
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!
-
I'm trying to get rid of this LEFT(@cell, 2) = "05", so I don't have to manually change the "05" each month to the corresponding month. I tried the formula above and leaving out the LEFT(@cell, 2) = "05", but I get "Incorrect Argument Set"
-
This formula worked!! I reversed the order of Priority and Date.
=COUNTIFS({Priority}, "P2", {2020 from Ethica Date}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
-
My apologies.
Current Month:
=COUNTIFS({2020 from Ethica Date}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Priority}, "P2")
Previous Month (accounts for Dec when current month is Jan):
=COUNTIFS({2020 from Ethica Date}, IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), {Priority}, "P2")
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!
-
The problem with my initial formulas is that I had initially misread your ranges. Sorry about that.
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!
-
Thanks so much for your help!
-
Happy to help! 👍️
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!
-
@Paul Newcome having an issue I have a formula and it continues to return 0
=COUNTIFS([Name]:[Name], "XXX", [Name]:[Name], "YYY")
I know that's not correct how can the count function count X & Y
Senior Program Coordinator
De Anza College
-
Deleted. Misread the post. 😉
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.
-
Hi @Stacey Carrasco ,
Your formula is counting at the moment all occurrences where a cell in the Name cloumn hold the value XXX AND the value YYY at the same time. That's why you get a 0.
You probably want to count cases where the value is XXX OR YYY - this would be the formula:
=COUNTIF([Name]:[Name], "XXX") + COUNTIF([Name]:[Name], "YYY")
-
@Stacey Carrasco Werner is correct. Your formula is looking for a cell that contains two separate values at the same time which isn't possible.
You can either use Werner's solution or if you have other criteria you would like to eventually work into the COUNTIFS you can use an OR statement like so:
=COUNTIFS(Name:Name, OR(@cell = "XXX", @cell = "YYY"))
Using the OR will allow you to work in more range/criteria sets or additional names without having to update multiple COUNTIFS statements.
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 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
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!