SUMIF result including values not specified in sum-range

E

evans.notch.cabin

I am using SUMIF to sum monthly totals into a single worksheet, with values
coming from individual weekly worksheets (these are in the same workbook).
The result is including values that are not in the specified sum_range -
specifically from column D. It is only including the value from column D
once (vs. 5 times, once for each weekly worksheet).

Complete formula is below, where
'Monthly Totals'!\$B10 is criteria
B:O is range in each Weekly worksheet (ie, 'Week 1 STAFF'!\$B:\$O)
sum_range is K:K in each weekly worksheet
Criteria appears in column B in each Weekly worksheet
Erroneous value appears in column D in each Weekly worksheet

=SUMIF('Week 1 STAFF'!\$B:\$O,'Monthly Totals'!\$B10,'Week 1
STAFF'!K:K)+SUMIF('Week 2 STAFF'!\$B:\$O,'Monthly Totals'!\$B10,'Week 2
STAFF'!K:K)+SUMIF('Week 3 STAFF'!\$B:\$O,'Monthly Totals'!\$B10,'Week 3
STAFF'!K:K)+SUMIF('Week 4 STAFF'!\$B:\$O,'Monthly Totals'!\$B10,'Week 4
STAFF'!K:K)+SUMIF('Week 5 STAFF'!\$B:\$O,'Monthly Totals'!\$B10,'Week 5
STAFF'!K:K)

Any thoughts?

J

JE McGimpsey

Are you sure it's including values from column D?

Since your criteria range is B:O, the sum_range will be resized to K:X
(which of course overlap)

S

Shane Devenshire

Maybe you have the SumRange and the Critieria Range backwards?

More like:

=SUMIF(K:K,\$B10,\$B:\$O)

If this is not the case, then you need to explain what you are trying to do.
Are you trying to say If any item on a row between B and O is equal to B10
then sum K? Or are you trying to sum K for each match on the given row? It
is generally a bad idea to have the criteria, B10, in the criteria range as