Sumifs formula not working

**[email protected]** · 09-21-2009, 01:15 PM

=AVERAGEIFS($D$106:$D$2825,$D$106:$D$2825,">"&$I117,$D$106:$D$2825,"<"&$J117)

Returns = 0

But on a copy of the same worksheet, the same formula returns the correct value. Is there something wrong with my formula cotext?

Thank you,

Todd

**DonkeyOte** · 09-21-2009, 01:19 PM

Are you sure the values in D are numbers and not numbers stored as text ?

What does

=COUNT(D106:D2825)

return ?

If 0 run Text to Columns on this range to coerce to number
(assuming they are constants and not result of formulae)

**[email protected]** · 09-21-2009, 01:23 PM

Yes they are formatted as numbers. I should also add that if I hardcode my < and >, the formula works.

**[email protected]** · 09-21-2009, 01:28 PM

=COUNT(D106:D2825)
returns 2720

**DonkeyOte** · 09-21-2009, 01:31 PM

Might be easier if you could post up the offending sheet in a sample file ?

When you said about the copy - did you copy the entire sheet or just the values onto another sheet ?

**[email protected]** · 09-21-2009, 01:59 PM

My problem is still with M110-M121.

Thanks for the assistance.

**DonkeyOte** · 09-21-2009, 02:26 PM

M won't calculate given it is dependent upon N being > 0 and N can never be greater than 0 given it counts how many cells are > J and < K and J > K. I think perhaps you need to correct N in the first instance, perhaps to reference I & J rather than J & K ?
ie the issue with the AVERAGEIFS calc is not in fact the AVERAGEIFS function at all.