Welcome!

Using a COUNTIFS formula if date criteria is met

Hello smartsheet community,

I am having some serious difficulty getting a COUNTIFS formula to work based on if a row in another sheet meets a data criteria.

My COUNTIFS formula works right now when referencing another sheet, but I would like to use the same formula and add onto it another function which will make it more accurate.

What I'd like to have is a formula which looks at another sheet and first looks at the DATE column which all have dates entered - the formula looks only at the rows with a date in the date column that is between X and Y dates - Then of those rows which have a date in the date column between X and Y dates - a COUNTIFS formula is performed on only those rows (I already have the COUNTIFS formula).

Please help! I have been going at it this whole week to no avail!

Find more posts tagged with

Comments

J. Craig Williams

=COUNTIFS({date range},> DATE(YY,MM,DD),{date range},< DATE(YY,MM,DD),etc...)

=COUNTIFS({date range},AND(> DATE(YY,MM,DD),< DATE(YY,MM,DD)),etc...)

Both should work.

For example

=COUNTIFS({PS | D}, >DATE(2018, 6, 12), {PS | D}, <DATE(2018, 6, 30))

>= and <= can also be used.

Craig

mwiggins

Thank you Craig,

Fortunately I was able to get it on my own using the DATE function in a more pronounced way as well as adding multiple COUNTIFS in the same link.

I will experiment with the use of AND that you showed, however, to see if there is still yet a more optimal method to doing this.

Thank you for the reply and help!

Sign in to join the conversation!

Quick Links

Trending discussions

Is it possible to edit an email list in an automated monthly report?
I have a monthly report I send out that has a long list of recipients. I know I can edit the recurrence of it being sent, but does anyone know of an easy way to just add or take out people that get my email without having to recreate the whole thing?
User Friendly Search
We would like to allow conference attendees to check themselves in using tablets at the conference center. I know there is a "find in sheet" magnifying glass in table view, but it is not super obvious, especially for non-Smartsheet users. Is there a better way for them to easily find their row among all the attendees so…
2 part automation delay question?
Greetings- I have a automation setup when I select the "status" column drop-down to "completed" I want the row info to be emailed to the contact to submitted it with a follow up then I want it to archive the row (aka move to another sheet). When I run the automation only 1 of the 2 requests happen. The archive happens…