# COUNTIFS with multiple range and same criteria

Options
edited 08/24/23

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

• ✭✭✭✭✭✭
Options

You would need three separate COUNTIFS added together (one for each range).

• Options

Hi Paul,

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?

• ✭✭✭✭✭✭
Options

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)

• Options

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

• ✭✭✭✭✭✭
Options

If you drop both sheets into a folder, you can "save as new" the folder, and the references should update that way.

• Options

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"?

• ✭✭✭✭✭✭
Options

When you "Save as new", make sure you have the proper selection made for the cell links and cross sheet references:

• Options

Thank you Paul,

That option is only available if you are copying an entire folder, but I'm only copying a sheet.

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!