What's wrong with my SUMIF?

[rocketD]

Hi All,

I am trying to pull numbers from one-sheet and average them in another
sheet by week number. I have it set up to sum all the numbers for a
particular week, then divide by the count of records with that week
value (so you have a running average, no matter what day of the week
it is). However, my sumif() function is NOT returning the appropriate
sums, so the averages are therefore wrong. I have checked and triple
checked the week-number values on which these calculations are based,
but there is absolutely no difference. They match exactly between
sheets.

Here is my sumif statement (using column A):
=SUMIF(Sheet1!$J:$J,Sheet2!$A64,Sheet1!A$7:A$2000)

Here's an example (assume first line is record #64, and that Sheet2!
$A64 = 2009_14) :
J A B C
2009_14 12,418 18,773 34,684
2009_14 0 21,174 24,767
2009_14 15,583 15,637 -1,157

TrueSum 28,001 55,583 58,294
TrueAvg 9,334 18,528 19,431

SUMIF Sum 46,748 46,911 -3,472
Formula Avg 15,583 15,637 -1,157

The formula averages are correct in that they divide the SUMIF sum by
3, which is the number of records for that week, but incorrect because
the SUMIF sum is wrong. Note how the formula averages = the last
record for each row.

Does anyone know what the heck is going on here?

 

[Bernard Liengme]

I notice that in =SUMIF(Sheet1!$J:$J,Sheet2!$A64,Sheet1!A$7:A$2000)
you have a full column range (J:J) and a partial column range (A7:A2000)

Change your formula to
=SUMIF(Sheet1!$J7:$J2000,Sheet2!$A64,Sheet1!A$7:A$2000)
and tell us if that helps
best wishes

 

[Dave Peterson]

Do you have hidden rows on Sheet1--either from Autofilter or hidden manually?

Maybe you're not seeing all the data.

 

[rocketD]

I notice that in =SUMIF(Sheet1!$J:$J,Sheet2!$A64,Sheet1!A$7:A$2000)
you have a full column range (J:J) and a partial column range (A7:A2000)

Change your formula to
=SUMIF(Sheet1!$J7:$J2000,Sheet2!$A64,Sheet1!A$7:A$2000)
and tell us if that helps
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email













- Show quoted text -

That was exactly the problem, thank you! I did make one minor
modification to maintain the criteria_range during the paste-down:
=SUMIF(Sheet1!$J$7:$J$2000,Sheet2!$A64,Sheet1!A$7:A$2000)

Thanks again for the suggestions. (No hidden rows in this case, btw,
but thanks anyway.)