Sumif - sum below or above lookup

[timsfca]

This seems to work, but I did not find any documentation on it.

Wondering if there are any known problems with the following SUMIF
approach:

=SUMIF(A1:A10, "word", B500:B510)

 

[Govind]

Hi,

For sumif, the criteria range ( A1:A10) and the sum range (B500:B510)
should be of the same range of cells. In your case, the formula will
work only if it is

=SUMIF(A1:A10, "word", B1:B10)

or

=SUMIF(A500:A510, "word", B500:B510)

Regards

Govind.

 

[timsfca]

That's the point. It DOES work. I am wondering if there are any known
problems associated with that approach, being that I can find no
documentation on it.

 

[Alex Delamain]

No - It is supposed to work with different ranges as explained in the
Excel Help file. If you could only sum on the same range as the "if"
applied to then you would not need the second range.
If "word" is in the third row of the "if" range it will add the value
that is third in the "sum" range.


Just for interest the two ranges don't even need to be the same size.
If the "If" range is longer than the "sum" range then it will still
include results outside the "sum" range.
If the Sum range is offset then it still justs counts down the rows to
match the position in the "if" range

 

[Tim Zych]

No - It is supposed to work with different ranges as explained in the

Excel Help file. If you could only sum on the same range as the "if"
applied to then you would not need the second range.

Don't see what that has to do with my original question.

 

[Bob Phillips]

Don't see what that has to do with my original question.

Are you joking?

 

[Tim Zych]

Are you joking?

No.

My question is whether or not the range to be summed can exist on a
different plane than the range to be looked up.

The help file does not address this.

It says:
--------------
Sum_range are the actual cells to sum. The cells in sum_range are summed
only if their corresponding cells in range match the criteria. If sum_range
is omitted, the cells in range are summed.
---------------

As you can see, there is no mention about whether the 2 arrays can be on
different "planes", e.g. A1:A100 to look in, B10000:B10100 to sum up.

The example it uses is:
----------------
SUMIF(A1:A4,">160000",B1:B4) equals $63,000
----------------

Alex responded to the effect that omitting the 2nd range would make the
Sumif formula sum up the lookup range.

That has absolutely nothing to do with my original question.

 

[Aladin Akyurek]

Don't worry: Nothing wrong with the setup as long as the sizes of
(vectorial) ranges of interest are the same.

 

[Myrna Larson]

My question is whether or not the range to be summed can exist on a
different plane than the range to be looked up.

The help file does not address this.

It says:
--------------
Sum_range are the actual cells to sum. The cells in sum_range are summed
only if their corresponding cells in range match the criteria. If sum_range
is omitted, the cells in range are summed.
---------------

As you can see, there is no mention about whether the 2 arrays can be on
different "planes", e.g. A1:A100 to look in, B10000:B10100 to sum up.

The example it uses is:

 

[Tim Zych]

Appreciate the confirmation Aladin.
(the first real answer to the post).

 

[Bob Phillips]

It also works if the

--

HTH

RP

Appreciate the confirmation Aladin.
(the first real answer to the post).

 

[Bob Phillips]

It also works if the ranges are different sizes, as Alex said