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"))
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"))
-
That did it! Thanks so much @Paul Newcome - you do amazing work!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 522 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!