CountIFs and Date range formula

VioletB
VioletB
edited 12/09/19 in Formulas and Functions

Hi,

need help with a formula. I am trying to count a type from one column 'Finding Rating' against a date range in another column 'Last Completed'. So far I have tried the following but they have all come up with UNPARSEABLE. FYI Conformance is one of the types that I need to count

=COUNTIFS({Finding Rating}, =Conformance, {Last Completed}, >= DATE(2019 7, 1), {Last Completed}, < DATE(2019, 9, 30))

=COUNTIFS(Finding Rating:Finding Rating, "Conformance", Last Completed, >=DATE(2019, 7, 1), Last Completed, <=DATE(2019, 9, 30))

=COUNTIFS({Finding Rating}, Conformance, AND {Last Completed}, (@cell >= DATE(2019, 07, 01), @cell <= DATE(2019, 09, 30)))

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Is the Finding Rating column on the other sheet as well? I notice you entered it as a cross sheet reference in the first formula and as being on the same sheet as the formula in the second one.

     

    If on the other sheet, your first formula should work if you put Conformance in quotes.

     

    =COUNTIFS({Finding Rating}, = "Conformance", {Last Completed}, >= DATE(2019 7, 1), {Last Completed}, < DATE(2019, 9, 30))

    thinkspi.com

  • Hi,

     

    Both the 'Finding Rating' and 'Last Completed' are on the same page. I have been trying to work out the formulas from other user's listing. didn't realise that I added a cross reference to another sheet.

    which formula do I need to use when both on the same sheet?

    thank you

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Violet,

    Try something like this.

    =COUNTIFS([Finding Rating]:[Finding Rating]; "Conformance"; [Last Completed]:[Last Completed]; >=DATE(2019; 7; 1); [Last Completed]:[Last Completed]; <DATE(2019; 9; 30))

    The same version but with the below changes for your and others convenience.

    =COUNTIFS([Finding Rating]:[Finding Rating], "Conformance", [Last Completed]:[Last Completed], >=DATE(2019, 7, 1), [Last Completed]:[Last Completed], <DATE(2019, 9, 30))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Andree's response below should work for you.

     

    When referring to a column on the same sheet as the formula, you enter the column name twice with a colon as a separator. You will use square brackets [ ] around the column names if there is a number, space, or special character.

     

    ColumnName:ColumnName

    ColumnA:ColumnA

    [Column A]:[Column A]

    [Column Name]:[Column Name]

    [Column1]:[Column1]

    [Column!]:[Column!]

    .

    When creating a cross sheet reference, the range will only be entered once with the range name surrounded by curly brackets { }.

    {Sheet Name Range 1}

    {Sheet Name Column Name}

    {Any other text you enter to specify a range name}

    thinkspi.com

  • Hi

    Apologies for taking so long to respond. I was away for work and only returned to office today.

    Yes Andree's formula has worked.

    thank you

    Violet

     

  • Hi Andree

    Apologies for taking so long to respond. I was away for work and only returned to office today.

    Formula has worked perferctly

    thank you

    Violet

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    No worries!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excellent! Glad we were able to get it working! yes

    thinkspi.com

  • These formulas worked for me too!

    I am wondering how to adapt the formula however. I want the formula to pick up a range of 56 days from today's date to 56 days prior. e.g.

    =COUNTIFS({Positive}, ="Yes", {Date}, >=DATE(2021, 11, 29), {Date}, <DATE(2022, 1, 16), {Location}, [email protected])

    The part of the formula in bold is what I'm missing. Every day new rows are added with todays date. I want it to be registering a continual 56 day period (8weeks) updating every day.

    Is this possible?