COUNTIFS with multiple range and same criteria
Hello Everyone,
I need help, to figure out how to do a COUNTIFS for the following:
I have 3 separate date columns (WorkDate, OutreachDate and ContactedDate). I want to do a count if any of the dates fall under the same calendar month. So it will be like an OR range and one criteria. Please advise. Thank you.
Jason
Answers
-
You would need three separate COUNTIFS added together (one for each range).
-
Hi Paul,
Thank you for your quick reply.
If I do it that way and say, I have 2 dates that fall on the same calendar month, would it give me a count of 2 or just 1? Each row is a list of patients, and if a patient was contacted and they did an outreach for another day but falls on the same calendar month, I need that to be a count of 1 only. Would that separate COUNTIFS give that result?
-
Separate COUNTIFS would provide a double count. There is a way to structure additional COUNTIFS to negate that, but it gets more complex with three columns.
My suggestion would be to add 4 helper columns. 3 text/number columns (all next to each other) and a multi-select dropdown.
In each of the text/number columns (one for each date) I would use:
=IF(MONTH([Date Column]@row)< 10, "0", "") + MONTH([Date Column]@row)
Then in the multi-select dropdown:
=JOIN([1st Helper]@row:[Last Helper]@row, CHAR(10))
And finally in your target sheet where the COUNTIFS is:
=COUNTIFS({Multi-Select}, HAS(@cell, "01"))
(for January)
-
Hi Paul,
That worked! It took me awhile to figure it out, I'm still trying to learn smartsheet. Anyway, appreciate the help.
If you don't mind me asking, is it possible if I "Save as New", that the references from the source sheet will move to the new source? I mean, say I have Sheet1 that has references that points to SheetA and then I Save as New and name it as Sheet2 but I want to point that to SheetB which has identical columns as SheetA only different data.
Thank you, Jason
-
If you drop both sheets into a folder, you can "save as new" the folder, and the references should update that way.
-
Yes, that was the first thing I did but the references still points to the original source. Do I have to change any "Save as New" options on "Automated Workflow & Alert Options" or Date & Formatting Options"?
-
When you "Save as new", make sure you have the proper selection made for the cell links and cross sheet references:
-
Thank you Paul,
That option is only available if you are copying an entire folder, but I'm only copying a sheet.
-
I thought you were taking Sheet A and Sheet 1 and making a copy of the set so that Sheet B is linked to Sheet 2?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!