S
Simon Livings
I have been using SUMIF a lot lately and have noticed an interesting
anomaly that I can't understand. Essentially, if sum_range is not equal
in size to the criteria range then Excel will automatically adjust the
range to be equal. For instance the formula:
=SUMIF(A1:A10,2,B1:B5)
will be treated as:
=SUMIF(A1:A10,2,B1:B10).
I suspect this is something to do with the fact that the sum_range is
an optional argument, but given the implicit assumption that the two
ranges should be the same size, you would imagine an error to occur if
this was entered (as with SUMPRODUCT).
I would be very interested to know if there is an explanation for this,
as errors could very easily creep into spreadsheets if your sum_range
was in fact correct and you had made a mistake in defining your
criteria range.
Any thoughts?
anomaly that I can't understand. Essentially, if sum_range is not equal
in size to the criteria range then Excel will automatically adjust the
range to be equal. For instance the formula:
=SUMIF(A1:A10,2,B1:B5)
will be treated as:
=SUMIF(A1:A10,2,B1:B10).
I suspect this is something to do with the fact that the sum_range is
an optional argument, but given the implicit assumption that the two
ranges should be the same size, you would imagine an error to occur if
this was entered (as with SUMPRODUCT).
I would be very interested to know if there is an explanation for this,
as errors could very easily creep into spreadsheets if your sum_range
was in fact correct and you had made a mistake in defining your
criteria range.
Any thoughts?