COUNTIFS using current month
Hi all,
I've been doing some searching on here and can't find the solution to my issue.
I have a smartsheet which has four columns. Three of them may have a name (text/number), the fourth is date only. I'm trying to run a formula that detects the total count of name X from columns 1-3, in the current month.
My formula is as follows, but I keep getting "INCORRECT ARGUMENT SET". I'm thinking it's something to do with the fact there's multiple columns to check the name against AND a date field, but I can't work out what I should do about it.
=COUNTIFS([Developer Assigned Date]:[Developer Assigned Date], IF(ISDATE(@cell), MONTH(@cell)) = MONTH(TODAY()), [Developer Assigned]:[OLT Developer Assigned], "David Acord")
Column 1 = Developer Assigned (text/number)
Column 2 = Secondary Developer Assigned (text/number)
Column 3 = OLT Developer Assigned (text/number)
Column 4 = Developer Assigned Date (date)
Best Answer
-
The issue is that your ranges do not match. one range is multiple columns and the other range is a single column (column type does not matter in this particular case). I would suggest a helper column where you join your three text/number columns and then incorporate a FIND/CONTAINS/HAS (whichever best suits you) in your COUNTIFS. My suggestion would be a multi-select dropdown for the Join Column and a HAS function in the formula.
=COUNTIFS([Developer Assigned Date]:[Developer Assigned Date], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), [Join Column]:[Join Column], HAS(@cell, "David Acord"))
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
-
The issue is that your ranges do not match. one range is multiple columns and the other range is a single column (column type does not matter in this particular case). I would suggest a helper column where you join your three text/number columns and then incorporate a FIND/CONTAINS/HAS (whichever best suits you) in your COUNTIFS. My suggestion would be a multi-select dropdown for the Join Column and a HAS function in the formula.
=COUNTIFS([Developer Assigned Date]:[Developer Assigned Date], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), [Join Column]:[Join Column], HAS(@cell, "David Acord"))
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!
-
That did it! Thanks so much @Paul Newcome - you do amazing work!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!