2

I have a sumifs formula with a filter range on a column with a year. Sometime this year is formatted as a number, other times it is formatted as text.

How can I make my sumifs formula flexible enough to handle both years formatted as numbers and as text?

For example, this formula returns incorrect results when passed a year formatted as text: =SUMIFS(C3:C6,B3:B6,"<>2016")

enter image description here

rvictordelta
  • 630
  • 2
  • 8
  • 23
  • 1
    *'formatted as text'* is not the same as using the `'` prefix to force text. Formatting cells as Text works while using the prefix doesn't. –  Sep 04 '18 at 15:00
  • Ok, it appears that the `'` prefix is being used. Is there a way to handle that, as well as numbers? – rvictordelta Sep 04 '18 at 15:20

2 Answers2

1

Assuming you are not using the ' prefix, mentioned by Jeeped, one way to do it is:

=SUM(C3:C7)-SUMIFS(C3:C7,B3:B7,"2016",B3:B7,2016)

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
1

"Text that looks like a number" (whether in the form '2016 or ="2016") can easily be turned into a "number" by adding 0 to it.

Try this, which should yield the correct result regardless of whether the B3:B6 range is a number or text that looks like a number:

= SUMIFS(C3:C6,(B3:B6)+0,"<>2016")

EDIT

You may have to use SUMPRODUCT here instead, e.g.

= SUMPRODUCT(C3:C6,(((B3:B6)+0)<>2016)+0)
ImaginaryHuman072889
  • 4,953
  • 7
  • 19
  • 51