SUMIF formula not working in Sheet Summary

Options

For some reason, I am getting an #Invalid Data Type error with my formula and cannot figure out why: =SUMIF([Fiscal Year]:[Fiscal Year], "2023", [Annual Revenue]:[Annual Revenue]). The two headers in question are text/number format with the revenue column being formatted in currency as shown below.

I tried to troubleshoot it with a coworker and hers worked without any error, even though it was configured exactly the same way as mine.

I also tried switching the sum column and the criterion in the formula, but that results in an #Incorrect Argument Set error. Please let me know what I can do to correct this. I suddenly have several #invalid data type or #invalid operations in other sheet summary fields for this worksheet, so not sure what's going on.

Thanks!

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Try it this way: =SUMIFS([Annual Revenue]:[Annual Revenue], [Fiscal Year]:[Fiscal Year], 2023)

  • Esquared213
    Esquared213 ✭✭✭
    Options

    @Nic Larsen - that still results in the #invalid data type error

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Hmm. Sounds like something is throwing it off in one of those columns. You can try wrapping it in an IFERROR like this and see if it works better.

    Copy and paste this one: =IFERROR(SUMIFS([Annual Revenue]:[Annual Revenue], [Fiscal Year]:[Fiscal Year], 2023), 0)

  • Esquared213
    Esquared213 ✭✭✭
    edited 11/20/23
    Options

    Yeah, I can't figure it out. I've been wondering if it sees the year as a number instead of a text value, but I have similar formulas everywhere in my workspaces and they are fine, even when they look at those same columns. It's only here in Sheet Summary that it's not working. Making your suggested change returned a 0 value, which is obviously incorrect, or that would have made for a very difficult sales revenue year. :o)

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    I'm stumped. Here is one last way I can think of:

    =SUMIFS([Annual Revenue]:[Annual Revenue], [Fiscal Year]:[Fiscal Year], IFERROR(YEAR(@cell), 0) = 2023)

  • Esquared213
    Esquared213 ✭✭✭
    Options

    Thanks for trying, @Nic Larsen, but still no luck. I wonder if it's possible this is a cache/cookies issue? It's just so weird. There's absolutely no reason the very first version shouldn't have worked, especially based on all the formulas I have everywhere else in my workspaces.

  • Genevieve P.
    Options

    Hiya!

    Just jumping in here to ask a bit more about the columns you're referencing...

    Formulas are unable to parse through a column if there's an error in even one of the cells its looking at. That means that even though the SUMIFS is structured correctly, if there's an error in either the [Fiscal Year] or [Annual Revenue] columns then it will bubble up into your current formula.

    Are there any formulas in those columns?

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

  • Esquared213
    Esquared213 ✭✭✭
    Options

    @Genevieve P. - that's a good thought, but the formula in the Annual Revenue column is so simple (monthly revenue *12). I don't see any errors in the column and it's a column formula. Other thoughts along those lines?

  • Genevieve P.
    Options

    Hey @Esquared213

    Is there any chance there's one row where the Monthly Revenue has text, so it's returning an error in a single cell?

    To get rid of this, we can add an IFERROR to your column formula. Try this:

    =IFERROR([Monthly Revenue]@row * 12, "")

    Cheers,

    Genevieve

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

  • Esquared213
    Esquared213 ✭✭✭
    Options

    @Genevieve P. - I added the IFERROR, but no blank cells resulted. It doesn't seem like there are any errors in this column. The Fiscal Year column is hand-keyed in.

  • Genevieve P.
    Options

    Hi @Esquared213

    Lets try two different formulas to determine where it's having trouble:

    =SUM([Annual Revenue]:[Annual Revenue])

    and

    =COUNTIFS([Fiscal Year]:[Fiscal Year], 2023)


    These should both return a number. If one of them is giving you an error or incorrect result, this will help us narrow down where it's getting stuck!

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

  • Esquared213
    Esquared213 ✭✭✭
    Options

    @Genevieve P. - Brilliant!! The issue has been identified - it's in the Fiscal Year column. Digging in deeper to find out why, but that should do the trick. Thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!