Using a sumif formula to lookup
values in three colums.
Formula wuz "SUMIF(w2:w25000,"jones",m3:m25000)"

This formula produced results off by a factor of ten or twenty.
The formula should have read "m2:m25000"
I didn't think having one row "m1/m2" in the SUMIF
formula would make such a difference.
The variable "jones" didn't have any values in
the top 2000 rows, much less the first two.

On Sat, 14 Jan 2006 07:59:34 -0500, "J Shrimps, Jr."
wrote:

Using a sumif formula to lookup
values in three colums.
Formula wuz "SUMIF(w2:w25000,"jones",m3:m25000)"

This formula produced results off by a factor of ten or twenty.
The formula should have read "m2:m25000"
I didn't think having one row "m1/m2" in the SUMIF
formula would make such a difference.
The variable "jones" didn't have any values in
the top 2000 rows, much less the first two.





You might check to be sure that you are matching the correct rows.

For example, in your function, a "jones" in w3000 would match the value in
m3001; and a jones in m25000 would not return anything, since the w25001 is not
included in your sum_range.


--ron

That is because it is matching one row off. For instance, say M3 said jones
and W2 was 1000 and W3 was 2000. The SUMIF would not match M3 against W3 and
return 2000, but against W2 and return 1000.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"J Shrimps, Jr." wrote in message
...
Using a sumif formula to lookup
values in three colums.
Formula wuz "SUMIF(w2:w25000,"jones",m3:m25000)"

This formula produced results off by a factor of ten or twenty.
The formula should have read "m2:m25000"
I didn't think having one row "m1/m2" in the SUMIF
formula would make such a difference.
The variable "jones" didn't have any values in
the top 2000 rows, much less the first two.

Ron Rosenfeld Wrote:
....and a jones in m25000 would not return anything, since the w25001 is
not
included in your sum_range.
--ron

This isn't quite correct. A jones in m25000 WOULD add the value in
W25001.

In SUMIF the actual sum range used is determined by the size of the
range, e.g. if you use the formula

=SUMIF(a1:a10,"apple",b1)

b1:b10 is still used as the sum range

see Excel help for further information


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=501335

Since the OP's range was

SUMIF(w2:w25000,"jones",m3:m25000)


then if there is a jones in W25000 it would return the value from
M25001 which if it is empty will return a zero. Don't know where jones in
the m
column came from since that is column the OP wants to sum?

--
Regards,

Peo Sjoblom

Portland, Oregon




"daddylonglegs"
wrote in message
news:daddylonglegs.21ngvm_1137284702.0856@excelfor um-nospam.com...

Ron Rosenfeld Wrote:
....and a jones in m25000 would not return anything, since the w25001 is
not
included in your sum_range.
--ron

This isn't quite correct. A jones in m25000 WOULD add the value in
W25001.

In SUMIF the actual sum range used is determined by the size of the
range, e.g. if you use the formula

=SUMIF(a1:a10,"apple",b1)

b1:b10 is still used as the sum range

see Excel help for further information


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=501335

On Sat, 14 Jan 2006 18:21:22 -0600, daddylonglegs
wrote:


Ron Rosenfeld Wrote:
....and a jones in m25000 would not return anything, since the w25001 is
not
included in your sum_range.
--ron

This isn't quite correct. A jones in m25000 WOULD add the value in
W25001.

In SUMIF the actual sum range used is determined by the size of the
range, e.g. if you use the formula

=SUMIF(a1:a10,"apple",b1)

b1:b10 is still used as the sum range

see Excel help for further information

Interesting. I didn't realize that.

In any event, the OP's problem possibly still stems from the sum range being
offset by 1.
--ron