INVALID DATA Type when using countifs formula referencing from another sheet

Hi,

I am computing for weekly counts of leads generated for my report and I am using countifs formula and refer to the working sheet where my data is located.

I am pretty sure that I am referencing in the correct Date Column and it's data type is also a Date. But the formula is prompting an "Invalid data type error". When I try to use the formula to the working sheet itself and select a range of cells, it is working. Please see the screenshot below.

The first image shows my formula referencing to the working sheet, but when I hit enter it prompts "INVALID DATA TYPE". The second image is the my reference column. The third image is when I tried the formula to the working sheet itself and it is working.

Please help on how can I fixed the "INVALID DATA TYPE" error.


Tags:

Answers

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

    Hi @Benjo09

    Try this.

    =COUNTIFS({Fresh Leads Master Range 1}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))

    Did that work?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi @Benjo09

    The error occurs when it's looking at the entire column, which includes blank cells that aren't dates. (You'll notice that for the formula that's in the sheet itself, you have a specific range listed - from rows 1-12, which is why it doesn't error).

    To fix this, we can just wrap an IFERROR statement around your WEEKNUMBER(@cell) function to say that if there's an error there (so, if the cell is not a date) just read it as "0" instead.

    Try this:

    =COUNTIFS({Fresh Leads Master Range 1}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))


    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!