Excel 2007 : sumifs formula resulting in 0

[RichGold]

I am trying to use sumifs to sum number values under multiple criteria. The formula is valid yet I receive 0 as the solution which it defintely is not. This is the formula:

=SUMIFS(DEKLAIM!C:C,DEKLAIM!A:A,F7+0,DEKLAIM!B:B,"work")
Any ideas or suggestions would really be appreciated.

[daddylonglegs]

If you use COUNTIFS to check the two conditions what do you get?

=COUNTIFS(DEKLAIM!A:A,F7+0,DEKLAIM!B:B,"work")

If that gives zero too then your problem is presumably with one or both of those conditions, what's in F7, why are you adding zero?

If the above gives a non-zero result then possibly the "numbers" in the sum range are formatted as text....in which case they won't sum. Try converting that range to numeric values, select column C and use

Data > Text to columns > Finish

[RichGold]

One of the criteria comes from a pivot table that queries an erp database. adding +0 changes the text format to number. When I apply the sumifs with only one criterion I receive a proper result regardless of which criterion I use. I tried your suggestion with countifs as well with the same result - one criterion brings a proper result, two criteria bring a result of 0.

[daddylonglegs]

OK, Rich, that sounds like everything is working OK - are you aware that SUMIFS will only sum the rows where both criteria are satisfied? Are you sure you have some rows where both criteria apply?

[RichGold]

Yes, I am aware. I am quit sure that in my example both criteria are satisfied. I wonder if this being a foreign language, right to left array ( Hebrew ) can effect the sumifs. I doubt it but I am going to try a few controlled experiments. Thanks for your help.