Countifs - Multiple Criteria - Same sheet but two different columns.
I'm trying to use the COUNTIFS to look in a range for a name and only give me those with the quarter of Q1 2021. When I try to combine I should see 4, but the formula is giving me 24.
=COUNTIFS({2021 X Projects - Marketing Range 3}, "Todd") + COUNTIFS({2021 X Projects - Marketing Range 2}, "Q1 2021")
Answers
-
I hope you're well and safe!
Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
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.
-
Currently you have two separate formulas: one counts how many times "Todd" is in a column, the other counts how many times "Q1 2021" is in the other column, then it adds these two numbers together. This means that if "Todd" is with "Q1 2021" you get a Count of 2. If "Todd" has a different quarter, you get a count of 1, since your first Count finds one match.
Are you looking to only count where these two criteria are on the same row? (Ex. both "Todd" and "Q1 2021")
If so, add these criteria into the same COUNTIFS function, like so:
=COUNTIFS({2021 X Projects - Marketing Range 3}, "Todd", {2021 X Projects - Marketing Range 2}, "Q1 2021")
Let me know if this works for you!
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Yes, I'm looking to count based on those two criteria. I will try your formula.
-
Hi Genevieve!
I tried the formula: =COUNTIFS({2021 X Projects - Marketing Range 1}, Todd",{2021 X Projects - Marketing Range 2}, "Q1 2021")
This comes back as #UNPARSEABLE
If I try with only one part of the formula like Todd or Q1 2021 it will give me the formula. But when they are together, I get the above error.
Same sheet but two different columns. I need to ensure the name is there and then make sure its only Q1 2021. Should be 4 but gives me all his Q1 - Q4, so its not doing the second part of the criteria or errors as above.
-
It looks like you're missing a quotation mark around "Todd" above, try this:
=COUNTIFS({2021 X Projects - Marketing Range 1}, "Todd", {2021 X Projects - Marketing Range 2}, "Q1 2021")
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Appreciative of the Community as a resource! I was able to find the formula I needed by searching the questions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!