SUMIF formula not working in Sheet Summary

Esquared213

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!

Nic Larsen

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

Esquared213

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

Nic Larsen

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

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

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

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.

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?

Esquared213

@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.

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

Esquared213

@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.

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!

Esquared213

@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!