COUNTIFS between dates & criteria.

Anne Crotteau

I'd love some help from the formula wizards! I'm trying to count the number of meetings we have for the month of June 2023. Criteria:

• I don't want to count meetings with unknown dates
• don't want to count two meetings with the same firm if those two meetings occurred on the same date
• I do want to count the meetings with the same firm if those meetings occurred on different dates

Can someone help me figure out why my formula isn't working?

=COUNTIFS([Last met on]1:[Last met on]5000, AND(@cell >= DATE(2023, 6, 1), @cell <= DATE(2023, 6, 30)))

Genevieve P.

Hey @Anne Crotteau

Based on your criteria, we need something to help eliminate the duplicate rows that have the same Firm Name and the same date.

What I would personally do is set up a checkbox column to flag only ONE row from the duplicates. You would need to also have a Created Date column in your sheet to differentiate the two (or three) rows. Then you can find the MAX Created Date for the duplicate rows and only check that one row.

=IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Last met on]:[Last met on], [Last met on]@row, [Firm name]:[Firm name], [Firm Name]@row)), 1, 0)

Then you can use this helper column as an additional filter in your COUNTIFS:

=COUNTIFS([Last met on]:[Last met on], @cell >= DATE(2023, 6, 1), [Last met on]:[Last met on], @cell <= DATE(2023, 6, 30), [Helper Column]:[Helper Column], 1)

Let us know if this works!

Cheers,

Genevieve

Nick Stamatakis

https://community.smartsheet.com/discussion/comment/351511#Comment_351511

Hey @Paul Newcome It's been a while but for whatever reason, as of yesterday, the formulas we used to get the output I've been using for almost a year stopped working. I haven't changed the formula, my sheets and columns have not changed. Did anything change with the formulas and how SS responds to them?

A bit odd that it would stop working yesterday (and today) when nothing has changed. Let me know if I am missing something... Thanks in advance!

Paul Newcome

@Nick Stamatakis Nothing that I am aware of. Exactly which formula isn't working, and how is it not working? Are you seeing an error or an incorrect output?

Nick Stamatakis

Once you helped me out with this formula, it's been working like a charm. For whatever reason, I now just keep getting Invalid Operation. The sheets are identical, the column types have stayed the same, so I am not sure what gives. I am open to anything; even if it means checking the simplest (and most overlooked) things :-)

Paul Newcome

@Nick Stamatakis Is that error present in even one cell in any of the columns being referenced?

Nick Stamatakis

@Paul Newcome Ha! I've got one row that randomly had its start and end dates change its format to this:

I think you can see the culprit. I've had this date format happen to me before but my solution from Smartsheet help desk was I believe to let it correct itself. This is definitely not a default date format I would have selected (and this project ended over a year ago for us so no reason to touch it).

Any thoughts as to why the date format has changed? How do I correct this myself without 'just waiting'?

Thanks Paul!

Paul Newcome

@Nick Stamatakis I've not seen it do that before, but I get where it's coming from. Try deleting it out of the source sheet, saving, refreshing, then putting it back in the source sheet and saving.

Nick Stamatakis

It's a Smartsheet miracle; thanks @Paul Newcome! Still can't figure out what causes that, especially when I don't touch it ever. There was no way to fix it other than breaking the linkage from the project plan to my metadata sheet to drive the correct date format down to the portfolio summary sheet. This allowed for the sheet summary value to populate in the corresponding widget in my dashboard.

If it is helpful to raise a ticket to sort out why that date format changed, let me know and I can submit a ticket to investigate again.

Paul Newcome

@Nick Stamatakis You could submit another support ticket, but honestly I'm not sure it will do any good. It seems like a random bug where it is grabbing the coded date/time stamp instead of just the date as it should.

Radial_Rob

Hi Paul,

I see you are the date Guru and was wondering if you could help me out. I am trying to count how many items numbers there are for a recruiter if items open open date is between 91 and 120 days ago from todays date.

The formula I used worked for 31-90 days

=COUNTIFS({Days Open}, >=TODAY(-31), {Days Open}, >=TODAY(-90), {Recruiter}, Name@row)

But does not work for 91-120 days

=COUNTIFS({Days Open}, >=TODAY(-91), {Days Open}, >=TODAY(-120), {Recruiter}, Name@row)

Any guidance would be much appreciated.

Paul Newcome

@Radial_Rob You may want to double check that your first formula is in fact pulling in the correct count. When you want to count dates that are BETWEEN two dates, one of your arguments should be less than or equal to and the other argument should be greater than or equal to. In both of your formulas, you have both arguments as greater than or equal to.

Try changing that first argument to less than or equal to and see if that works.

Think of dates as a set of numbers on a line with TODAY() being zero. You want dates that are less than or equal to -31 and greater than or equal to 90.