SUMIF - Sum_Range is misleading

[Epinn]

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

 

[Leo Heuser]

"Epinn" <[email protected]_SPAM> 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.

 

[Epinn]

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

"Epinn" <[email protected]_SPAM> 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.

 

[Leo Heuser]

"Epinn" <[email protected]_SPAM> 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







"Epinn" <[email protected]_SPAM> 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.