SUMIFS for negative numbers

[AwesomeSteph]

Is there a way to use SUMIFS for negative numbers?

What I started with:
=SUMIFS(QA!M:M,QA!D:D,">="&Summary!B1,QA!D:D,"<="&Summary!B2)
The result should be -11 (5 negative twos and 1 negative one) however the result is 258974

what I tried to fix it
=SUMIFS(QA!M:M,QA!M:M,"<0",QA!D:D,">="&Summary!B1,QA!D:D,"<="&Summary!B2)
this resulted with nothing.

 

[Joe4]

That should work, as long as your values are really entered as numbers and not text.
Try isolating the negative criteria part, like this:

[COLOR=#333333]=SUMIFS(QA!M:M,QA!M:M,"<0")
[/COLOR]

Does that sum up the negative values values from column M on the "QA" sheet?
If so, then the issue is actually with your other criteria and not the negative part.
If it does not sum up anything then either:
- You are looking at the wrong range (wrong sheet or column)
- Your values are entered as text and not numbers
- You have no negative values in your range

 

[AwesomeSteph]

Thank you for your help!!

 

[Joe4]

You are welcome.

So, if you don't mind me asking, what ended up being the issue?

 

[AwesomeSteph]

Formatting. It was not numbers. I feel like that should have been the first thing I looked for.

 

[Joe4]

Ah yes, a common culprit!
Glad you got it sorted out.

 

[rlong98]

Hello,
Leila has a video on 'sumifs' between dates

I have the same type of sheet i'm testing on - with NEGATIVE numbers, and it returns 0

So if you had her exact sheet - how would the formula look?

Thank you

 

[Joe4]

I have the same type of sheet i'm testing on - with NEGATIVE numbers, and it returns 0

So if you had her exact sheet - how would the formula look?

Just like was mentioned previous in this post, the very first thing to verfy:
- Are you number REALLY entered as numbers, or are they entered as text?
- Are you dates REALLY entered as dates, or are they entered as text?

Note that "dates" are really treated as numbers in Excel. So it is really easy to verify if you numeric and date entries are, in fact, numbers and dates using the ISNUMBER function.

Pick any number entry. Let's say it is cell A1. Then enter this formula in any empty cell:
=ISNUMBER(A1)

Now pick any cell with a date entry on your sheet. Let's say cell B1 has a date entry. Then enter this formula in any empty cell:
=ISNUMBER(B1)

What do those two formulas return?
They should both return TRUE. Is either returns FALSE, you have Text entries, and that is why your formulas won't work.

If they return True, and your SUMIFS formula is not working, please post the SUMIFS formula you are trying.