Need help with COUNTIFS in sheet summary utilizing 2 columns from the same sheet

I have a sheet with Start Date (date properties), which I have converted to Current Year (text/number properties) in a separate column and then another column called Fully Vaccinated? with dropdown single select properties. In the sheet summary, I have the following formula:

=COUNTIFS([Fully Vaccinated?]:[Fully Vaccinated?], "Yes", [Current Year]:[Current Year], "2022")

This formula returned values when initially created a week ago and when accessing yesterday, I'm receiving #INVALID DATA TYPE error message.

I would appreciate any advice or guidance on what I'm missing.

Thanks,

Sherry

Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Sherry Moran

    What is the formula you are using to take the year from the date field?

    You might also get an error if your Current Year or Start Date columns contain blanks.

    You could also skip that whole extraction of the year if you want by putting the YEAR function inside your COUNTIFS:

    =COUNTIFS([Fully Vaccinated?]:[Fully Vaccinated?], "Yes", [Start Date]:[Start Date], ISDATE(@cell), [Start Date]:[Start Date], YEAR(@cell) = "2022")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Sherry Moran

    What is the formula you are using to take the year from the date field?

    You might also get an error if your Current Year or Start Date columns contain blanks.

    You could also skip that whole extraction of the year if you want by putting the YEAR function inside your COUNTIFS:

    =COUNTIFS([Fully Vaccinated?]:[Fully Vaccinated?], "Yes", [Start Date]:[Start Date], ISDATE(@cell), [Start Date]:[Start Date], YEAR(@cell) = "2022")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • @Jeff Reisman

    Thanks so much for your help - yes, this worked.

    I used =YEAR([Start Date]@row) to convert to the year, but what you've suggested works for what I need. Appreciate the help!

    Sherry Moran

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 03/02/22

    @Sherry Moran

    Glad it worked.

    For those keeping score at home:

    Formulas in Smartsheet work from left to right. The [Start Date]:[Start Date], ISDATE(@cell) part of the formula eliminates from consideration any fields from the Start Date column that aren't a date value. It's set to the left of the [Start Date]:[Start Date], YEAR(@cell) = "2022" part, so that when then formula then evaluates for the YEAR value, it's only looking at cells that have a date value in them; thus, it does not error.

    If I were to switch the order, it would fail:

    Here it works:

    Here it does not:


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • @Jeff Reisman

    Thanks for the explanation. Awesome help!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!