CODEWITHSUNDEEP
excelexcel-formula
Ritu Mishra
Ritu Mishra

Reputation: 83

The SUMIF function does not work with a specific column (the sum range column)

I am trying to use the SUMIF function to sum a specific column on another sheet based on just one criteria.

I have done this 1,000 times before. The formula works for every single column except this one and I don't know why. I have tried so many things (changing the range to general or to numbers, etc.; it literally works for summing every other column except this one). The range and criteria remain the same. It is just the sum range which I am changing.

No idea what is going on. Please help!

Upvotes: 2

Views: 4996

Answers (1)

A.S.H
A.S.H

Reputation: 29332

SUMIF(range, criteria, [sum_range]) like many other functions that operate on numbers, expect the specified sum_range to be numeric. It happens that sometimes the users sees numbers in some columns but these ranges are actually Text.

This can happen in two situations:

  • The data was entered as text preceded by a quote, i.e. '100

  • The data results from a formula that generates text, i.e. =RIGHT("a123", 3)

In these cases SUMIF will treat these cells as text and ignore the numeric values. Unfortunately, using VALUE(range) in these cases won't help, because the function SUMIF wants a range parameter and does not accept a calculated array.

This was the case with the OP's range in question: column N was generated by the formula:

=IF(M2<20,"1",IF(AND(M2>19,M2<51),"2",IF(M2>50,"3")))

and the solution was to change the initial formula so that it generates numbers instead of texts, by removing the useless double-quotes around the numbers:

=IF(M2<20,1,IF(AND(M2>19,M2<51),2,IF(M2>50,3)))

Upvotes: 1

Related Questions