COUNTIFS & Date Ranges

ElissaM

Hi,

I am struggling with the correct syntex for a formula that counts the number of instances of a label "Sellers" in an external worksheet for a specific time period. I have attempted to apply the formula examples I have found in this community however I continue to receive Unparseable errors.

The Date column properties has been set as "Date"

The {APJ Enablment Tracker Range x } refers to the colum in a seperate smartsheet.

=COUNTIFS({APJ Enablement Tracker Range 1}, "Sellers", AND({APJ Enablement Tracker Range 2}, (@Cell >= DATE(2021, 09 , 30), @Cell <=DATE(2021, 10, 30)))

Appreciate any guidence.

Elissa

ElissaM

Hi Bassam,

Thank you for the suggestion. Unfortunately the @Cell function did not work. I ended up using the following formula (which is I know is not efficent) but it was the only one that worked.

=COUNTIFS(APJ Enablement Tracker Range 3}, ="as A Service", {APJ Enablement Tracker Range 2}, >=DATE(2021, 9, 1), {APJ Enablement Tracker Range 2}, <=DATE(2021, 10, 30))

Bassam Khalil

Hi @ElissaM 

Hope you are fine, please try the following formula:

=COUNTIFS({APJ Enablement Tracker Range 1}, @cell="Sellers",
 AND({APJ Enablement Tracker Range 2}, (@Cell >= DATE(2021, 09 , 30),
 @Cell <=DATE(2021, 10, 30)))

ElissaM

Hi Bassam,

Thank you for the suggestion. Unfortunately the @Cell function did not work. I ended up using the following formula (which is I know is not efficent) but it was the only one that worked.

=COUNTIFS(APJ Enablement Tracker Range 3}, ="as A Service", {APJ Enablement Tracker Range 2}, >=DATE(2021, 9, 1), {APJ Enablement Tracker Range 2}, <=DATE(2021, 10, 30))

marisadyw

Hello,

I am also struggling with my formula syntax for =COUNTIFS that includes a date range.

This is what I have so far: =COUNTIFS([Date Services Started]:[Date Services Started], AND (@cell >= DATE(2022, 12, 01), @cell <= DATE(2022, 12, 15)), [EHDSC Services Used]:[EHDSC Services Used], HAS(@cell, "Bioinformatics - Transcriptomics"))

Is there any issues you see with my syntax that can help me produce a successful formula?

Thank you!

Genevieve P.

Hi @marisadyw

Can you post a screen capture with the formula written in the sheet?

From what I can see, the only issue is a space between AND and it's opening parentheses. For example, you have:

AND (

instead of

AND(

Try this:

=COUNTIFS([Date Services Started]:[Date Services Started], AND(@cell >= DATE(2022, 12, 01), @cell <= DATE(2022, 12, 15)), [EHDSC Services Used]:[EHDSC Services Used], HAS(@cell, "Bioinformatics - Transcriptomics"))

Cheers,

Genevieve

marisadyw

Hi @Genevieve P.

Here's the screenshot!

Thank you in advance for your help.

Genevieve P.

Hi @marisadyw

Thank you for this image! I can see here that there's still a space between the AND and the (

Can you try removing that, and let me know if that works?

AND (

Should be:

AND(

Cheers,

Genevieve

marisadyw

Hi @Genevieve P.

Even removing the space between the AND and ( still gives me an #UNPARSEABLE response.

Any other tips?

Thanks,

Marisa

Genevieve P.

Hi @marisadyw

It looks like you may have a different column name typed into the formula compared to what's in your sheet. I see in your image it looks like your column name is:

Date Service Started

but in your formula it's typed as:

Date Services Started

Try removing the s at the end and let us know if we figured it out! 🙂

marisadyw

@Genevieve P. Aha! Thank you so much!!

Genevieve P.

Haha no problem at all! I missed it, too.