Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF Problem
I'm trying to use SUMIF and the results appear to be wrong. I've checked to
be sure that the values in column B are truly numbers (not text) and they are numbers. Column C1 is where the formula resides and the sum should be 583.50. I watched what happening using the Function Wizard, and for the sum_range it is = {516;67.50}. Also tried taking the = out of the criteria argument and it doesn't matter. Any ideas? =SUMIF(A1:A1,"=2",B1:B2) A B C 2 516.00 516 1 67.50 Thanks for your help, -- Julie |
#2
|
|||
|
|||
SUMIF Problem
Thanks for bearing with me. Did you try =sumif(A1:A1,2,B1:B2)? According to
their help text, "sum_range does not have to be the same size and shape as range." It seems for the sum_range it's ignoring the rows in column A that contain 1 instead of 2. Thanks for taking the time. -- Julie "Ray A" wrote: Julie, I entered your identical data and in C1 entered =sumif(A:A,2,B:B) and it worked fine for me "julie" wrote: Ray- Thanks. I did try that (just tried it again). It doesn't make a difference. I also made sure column A was numeric by-the-way. Any other thoughts would be appreciated. -- Julie "Ray A" wrote: Hi Julie, Remove the " marks and the equal sign. The " signify text and the = sign is not needed HTH "julie" wrote: I'm trying to use SUMIF and the results appear to be wrong. I've checked to be sure that the values in column B are truly numbers (not text) and they are numbers. Column C1 is where the formula resides and the sum should be 583.50. I watched what happening using the Function Wizard, and for the sum_range it is = {516;67.50}. Also tried taking the = out of the criteria argument and it doesn't matter. Any ideas? =SUMIF(A1:A1,"=2",B1:B2) A B C 2 516.00 516 1 67.50 Thanks for your help, -- Julie |
#3
|
|||
|
|||
SUMIF Problem
Since you want the sum or not depending only on a single cell, use
=IF(A1=2,SUM(B1:B2),0) You have misinterpreted the remark that you quote from Excel 2003's Help. Before the remark, Help states that cells are added "if their corresponding cells in range match criteria", and the examples show that the sum_range will be extended to match the size of the match range if it is not the same size or shape. There is no example to suggest that the match range might be extended (as you are wanting) to match the size and shape of sum_range. Since there is no cell in the match Range corresponding to B2 in your formula, B2 will never under any circumstance be included in the sum. Jerry "julie" wrote: Thanks for bearing with me. Did you try =sumif(A1:A1,2,B1:B2)? According to their help text, "sum_range does not have to be the same size and shape as range." It seems for the sum_range it's ignoring the rows in column A that contain 1 instead of 2. Thanks for taking the time. -- Julie "Ray A" wrote: Julie, I entered your identical data and in C1 entered =sumif(A:A,2,B:B) and it worked fine for me "julie" wrote: Ray- Thanks. I did try that (just tried it again). It doesn't make a difference. I also made sure column A was numeric by-the-way. Any other thoughts would be appreciated. -- Julie "Ray A" wrote: Hi Julie, Remove the " marks and the equal sign. The " signify text and the = sign is not needed HTH "julie" wrote: I'm trying to use SUMIF and the results appear to be wrong. I've checked to be sure that the values in column B are truly numbers (not text) and they are numbers. Column C1 is where the formula resides and the sum should be 583.50. I watched what happening using the Function Wizard, and for the sum_range it is = {516;67.50}. Also tried taking the = out of the criteria argument and it doesn't matter. Any ideas? =SUMIF(A1:A1,"=2",B1:B2) A B C 2 516.00 516 1 67.50 Thanks for your help, -- Julie |
#4
|
|||
|
|||
SUMIF Problem
Thank you. That makes sense.
-- Julie "Jerry W. Lewis" wrote: Since you want the sum or not depending only on a single cell, use =IF(A1=2,SUM(B1:B2),0) You have misinterpreted the remark that you quote from Excel 2003's Help. Before the remark, Help states that cells are added "if their corresponding cells in range match criteria", and the examples show that the sum_range will be extended to match the size of the match range if it is not the same size or shape. There is no example to suggest that the match range might be extended (as you are wanting) to match the size and shape of sum_range. Since there is no cell in the match Range corresponding to B2 in your formula, B2 will never under any circumstance be included in the sum. Jerry "julie" wrote: Thanks for bearing with me. Did you try =sumif(A1:A1,2,B1:B2)? According to their help text, "sum_range does not have to be the same size and shape as range." It seems for the sum_range it's ignoring the rows in column A that contain 1 instead of 2. Thanks for taking the time. -- Julie "Ray A" wrote: Julie, I entered your identical data and in C1 entered =sumif(A:A,2,B:B) and it worked fine for me "julie" wrote: Ray- Thanks. I did try that (just tried it again). It doesn't make a difference. I also made sure column A was numeric by-the-way. Any other thoughts would be appreciated. -- Julie "Ray A" wrote: Hi Julie, Remove the " marks and the equal sign. The " signify text and the = sign is not needed HTH "julie" wrote: I'm trying to use SUMIF and the results appear to be wrong. I've checked to be sure that the values in column B are truly numbers (not text) and they are numbers. Column C1 is where the formula resides and the sum should be 583.50. I watched what happening using the Function Wizard, and for the sum_range it is = {516;67.50}. Also tried taking the = out of the criteria argument and it doesn't matter. Any ideas? =SUMIF(A1:A1,"=2",B1:B2) A B C 2 516.00 516 1 67.50 Thanks for your help, -- Julie |
#5
|
|||
|
|||
SUMIF Problem
You're welcome. Glad it helped
Jerry julie wrote: Thank you. That makes sense. -- Julie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
problem office assistant | Excel Discussion (Misc queries) | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
SUMIF problem | Excel Worksheet Functions |