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?

The sum range is proportional to the criteria range *even if you didn't
explicitly define it that way*.

Can't really tell what you're trying to do. Your sum range is within your
criteria range.


--
Biff
Microsoft Excel MVP


"evans.notch.cabin" wrote in
message ...
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?

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)


In article ,
evans.notch.cabin wrote:

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?

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
in your example.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"evans.notch.cabin" wrote:

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?