COUNTIFS between dates & criteria.

Paul Newcome

@Michelle Bestercy Happy to help. 👍️

Amna Collins

@Paul Newcome, I am impressed with your Smartsheet wizardry!

I am traying to work with the COUNTIFS formula to:

• Count by Platform column if between 01/01/2021-12/31/2021 in the Actual End column
• Struggling with the Platform that has one-letter value (X or S) as X keeps getting counted in both the X and Xi platforms
• Currently trying the following formula =COUNTIFS({Platform}, CONTAINS("X", @cell), {Actual End}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 12, 31)))

Any insight is much appreciated.

Paul Newcome

@Amna Collins What are the chances that "X" and "Xi" will be in the same cell, or will it always be one or the other?

Janneh Wright

@Paul Newcome for the countifs formula is it possible to use the =today() in the formula. I want to count all items given the dates are between today and 90 ago from today.

Paul Newcome

@Janneh Wright Yes. It would look something like this...

=COUNTIFS({Platform}, CONTAINS("X", @cell), {Actual End}, AND(@cell >= TODAY(-90), @cell <= TODAY()))

DonnaA

I have been reading this thread along with several other help documents in the Smartsheet Help Center, and I cannot figure out why my formula will not work. I am trying to get a count of the items in the Routing column that fall between 01/01/2022 and 01/31/2022.

=COUNTIFS({Routing}, >=DATE(2022, 1, 1), {Routing}, <=DATE(2022, 1, 31))

My column properties and column look like this

I am getting the error "Invalid Reference" which seems to indicate the it cannot find the column in within the {}, but the column is there and pretty simply named. I have double and triple checked my spelling.

I appreciate any help on what I am doing wrong.

Thank you,

Donna

Paul Newcome

@DonnaA How exactly are you creating your cross sheet reference?

DonnaA

@Paul Newcome I don't need a cross reference to a different sheet. The data and the formula are in the same sheet. Reviewing my formula in light of your question has prompted me to change it to

=COUNTIFS([Routing] , >=DATE(2022, 1, 1), [Routing], <=DATE(2022, 1, 31))

My error has now changed from Invalid Reference to Unparseable

Donna

Paul Newcome

Try this:

=COUNTIFS(Routing:Routing, >=DATE(2022, 1, 1), Routing:Routing, <=DATE(2022, 1, 31))

DonnaA

@Paul Newcome

I think mine looks just like yours, but it continues to report Unparseable

=COUNTIFS(Routing:Routing, >=DATE(2022, 1, 1), Routing:Routing, <=DATE(2022, 1, 31))

Paul Newcome

@DonnaA What are the column names actually being used in the sheet?

AmberH

Hello Paul,

I am trying to use COUNTIF to count how many rows do not contain "N/A" and between a date range. I keep getting #INCORRECT ARGUMENT SET

The formula is working on the rows that are pulling HAS(@cell, "XYZ"), but not <>"N/A"

Working Formula:

=COUNTIFS({Range 1}, <=DATE(2022, 12, 31), {Range 1}, >=DATE(2022, 1, 1), {Range 2}, HAS(@cell, "XYZ"))

Not Working Formula:

=COUNTIF({Range 1}, <=DATE(2022, 12, 31), {Range 1}, >=DATE(2022, 1, 1), {Range 2}, <>"N/A")

What am I doing wrong here?

Genevieve P.

Hi @AmberH

It looks like your second formula is COUNTIF (singular) instead of COUNTIFS, plural.

Try adding the S and it should work!

Cheers,

Genevieve

AmberH

Thank you @Genevieve P., That worked!! Silly me.. :)

WendyR

Hi,

Trying to count all the rows in the Estimated Metro E Install Date column that fall between August 22, 2022 and September 21, 2022 and it keeps failing. HELP!

FISCAL.xlsx

=COUNTIFS({Estimated Metro E Install Date}, <=DATE(2022, 9, 21), {Estimated Metro E Install Date}, >=DATE(2022, 8, 22))