COUNTIFS with multiple range and same criteria

jcasorla1

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

Paul Newcome

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

jcasorla1

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?

Paul Newcome

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)

jcasorla1

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

Paul Newcome

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

jcasorla1

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

Paul Newcome

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

jcasorla1

Thank you Paul,

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

Paul Newcome

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?