SUMIF result including values not specified in sum-range



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

Any thoughts?



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)



Shane Devenshire

Maybe you have the SumRange and the Critieria Range backwards?

More like:


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
in your example.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question