Invalid Data when wanting to count blanks

kioshi43
kioshi43 ✭✭✭
edited 12/21/22 in Formulas and Functions

Hi all,

I have a roll up sheet where I'm referencing another sheet to get some metrics. One of them is to count which grouping a building belongs to and since I know sometimes the building doesn't always have a grouping, I wanted to include a count of those unassigned buildings. But when I try a "" or a ISBLANK(@cell), it comes up with an Invalid Data type. Here's my formula:

=COUNTIFS({Grouping}, "", {Extract}, MONTH(@cell) = Month@row, {Extract}, YEAR(@cell) = Year@row)

What is odd about it too is that this seems to be specific to the COUNTIFS formula since I have another one that is written virtually the same, except it is a SUMIFS:

=SUMIFS({RSF}, {Grouping}, "", {Extract}, MONTH(@cell) = Month@row, {Extract}, YEAR(@cell) = Year@row)

Anyone have any thoughts?

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @kioshi43

    It may actually be the MONTH and YEAR functions giving you the error, not the "". Sometimes these functions can return an error if they're reading blank cells or cells with text in them. Try (in both of your formulas) wrapping an IFERROR around each one:

    =COUNTIFS({Grouping}, "", {Extract}, IFERROR(MONTH(@cell), 0) = Month@row, {Extract}, IFERROR(YEAR(@cell), 0) = Year@row)

    and

    =SUMIFS({RSF}, {Grouping}, "", {Extract}, IFERROR(MONTH(@cell), 0) = Month@row, {Extract}, IFERROR(YEAR(@cell), 0) = Year@row)

    Let me know if that helped!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @kioshi43

    It may actually be the MONTH and YEAR functions giving you the error, not the "". Sometimes these functions can return an error if they're reading blank cells or cells with text in them. Try (in both of your formulas) wrapping an IFERROR around each one:

    =COUNTIFS({Grouping}, "", {Extract}, IFERROR(MONTH(@cell), 0) = Month@row, {Extract}, IFERROR(YEAR(@cell), 0) = Year@row)

    and

    =SUMIFS({RSF}, {Grouping}, "", {Extract}, IFERROR(MONTH(@cell), 0) = Month@row, {Extract}, IFERROR(YEAR(@cell), 0) = Year@row)

    Let me know if that helped!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • kioshi43
    kioshi43 ✭✭✭

    Appreciate the help, thank you! Sorry, I didn't realize I hadn't replied ugh

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!