Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF - Sum_Range is misleading
A2:A5 = 100, 200, 300, 400 respectively.
B2:B5 = 5, 15, 30, 40 respectively. A1 and B1 are both blanks. =SUMIF(A2:A5,"300",B2:B5) yields 30. Okay. I know I shouldn't do the following but if I have a typo, I prefer Excel to give me an error ...... This is confusing. =SUMIF(A2:A5,"300",B1:B2) yields 15. ??? B1 = blank and B2 = 5. =SUMIF(A2:A5,"300",B2:B3) yields 30. ??? B2 = 5 and B3 = 15. =SUMIF(A2:A5,"300",B3:B4) yields 40. ??? B3 = 15 and B4 = 30. I rather have an error telling me of uneven range. Any comments? Epinn |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF - Sum_Range is misleading
"Epinn" skrev i en meddelelse
... A2:A5 = 100, 200, 300, 400 respectively. B2:B5 = 5, 15, 30, 40 respectively. A1 and B1 are both blanks. =SUMIF(A2:A5,"300",B2:B5) yields 30. Okay. I know I shouldn't do the following but if I have a typo, I prefer Excel to give me an error ...... This is confusing. =SUMIF(A2:A5,"300",B1:B2) yields 15. ??? B1 = blank and B2 = 5. =SUMIF(A2:A5,"300",B2:B3) yields 30. ??? B2 = 5 and B3 = 15. =SUMIF(A2:A5,"300",B3:B4) yields 40. ??? B3 = 15 and B4 = 30. I rather have an error telling me of uneven range. Any comments? Epinn It's not really an error. "Normally" the third argument is a range "parallel" to the range in the first argument, but this need not be so. Actually the third argument need only one cell, Excel does the rest! If you have a first argument of A2:A5 and a third argument of B1, Excel will resize B1 to hold 4 cells, i.e. B1:B4. Likewise for B3:B4, which will be resized to B3:B6. Now, =SUMIF(A2:A5,300,B1) will return 15, because 300 is in the third cell in A2:A5 and Excel will grab the value in the *corresponding third cell* in B1:B4, i.e. B3. The same principle goes for your two other examples. -- Best regards Leo Heuser Followup to newsgroup only please. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF - Sum_Range is misleading
Thank you for the explanation. It is all clear now. Even one cell does it, eh? Interesting. Are there any other functions using this principle as well?
Epinn "Leo Heuser" wrote in message ... "Epinn" skrev i en meddelelse ... A2:A5 = 100, 200, 300, 400 respectively. B2:B5 = 5, 15, 30, 40 respectively. A1 and B1 are both blanks. =SUMIF(A2:A5,"300",B2:B5) yields 30. Okay. I know I shouldn't do the following but if I have a typo, I prefer Excel to give me an error ...... This is confusing. =SUMIF(A2:A5,"300",B1:B2) yields 15. ??? B1 = blank and B2 = 5. =SUMIF(A2:A5,"300",B2:B3) yields 30. ??? B2 = 5 and B3 = 15. =SUMIF(A2:A5,"300",B3:B4) yields 40. ??? B3 = 15 and B4 = 30. I rather have an error telling me of uneven range. Any comments? Epinn It's not really an error. "Normally" the third argument is a range "parallel" to the range in the first argument, but this need not be so. Actually the third argument need only one cell, Excel does the rest! If you have a first argument of A2:A5 and a third argument of B1, Excel will resize B1 to hold 4 cells, i.e. B1:B4. Likewise for B3:B4, which will be resized to B3:B6. Now, =SUMIF(A2:A5,300,B1) will return 15, because 300 is in the third cell in A2:A5 and Excel will grab the value in the *corresponding third cell* in B1:B4, i.e. B3. The same principle goes for your two other examples. -- Best regards Leo Heuser Followup to newsgroup only please. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF - Sum_Range is misleading
"Epinn" skrev i en meddelelse
... Thank you for the explanation. It is all clear now. Even one cell does it, eh? Interesting. Are there any other functions using this principle as well? Epinn You are welcome. Thanks for the feedback. I'm not aware of any other function using this principle. A candidate might have been LOOKUP() (the vector form), but it takes at least 2 cells in the third argument, no matter how many cells are in the first argument (interesting in itself). Leo Heuser "Leo Heuser" wrote in message ... "Epinn" skrev i en meddelelse ... A2:A5 = 100, 200, 300, 400 respectively. B2:B5 = 5, 15, 30, 40 respectively. A1 and B1 are both blanks. =SUMIF(A2:A5,"300",B2:B5) yields 30. Okay. I know I shouldn't do the following but if I have a typo, I prefer Excel to give me an error ...... This is confusing. =SUMIF(A2:A5,"300",B1:B2) yields 15. ??? B1 = blank and B2 = 5. =SUMIF(A2:A5,"300",B2:B3) yields 30. ??? B2 = 5 and B3 = 15. =SUMIF(A2:A5,"300",B3:B4) yields 40. ??? B3 = 15 and B4 = 30. I rather have an error telling me of uneven range. Any comments? Epinn It's not really an error. "Normally" the third argument is a range "parallel" to the range in the first argument, but this need not be so. Actually the third argument need only one cell, Excel does the rest! If you have a first argument of A2:A5 and a third argument of B1, Excel will resize B1 to hold 4 cells, i.e. B1:B4. Likewise for B3:B4, which will be resized to B3:B6. Now, =SUMIF(A2:A5,300,B1) will return 15, because 300 is in the third cell in A2:A5 and Excel will grab the value in the *corresponding third cell* in B1:B4, i.e. B3. The same principle goes for your two other examples. -- Best regards Leo Heuser Followup to newsgroup only please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
How to use SUMIF function with non-adjacent cells | Excel Worksheet Functions | |||
SUMIF function help | Excel Worksheet Functions | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions |