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!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!