Sumif - sum below or above lookup

  • Thread starter Thread starter timsfca
  • Start date Start date
T

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)
 
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.
 
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.
 
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
 
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.
Click to expand...

Don't see what that has to do with my original question.
 
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.
 
Don't worry: Nothing wrong with the setup as long as the sizes of
(vectorial) ranges of interest are the same.
 
Please try it and post back. That's the quickest way to get the answer said:
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:
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF with criteria "<>" & "=" 4
Sumif using Arrays 2
subtotal with sumif nested 1
How do I use countif like sumif ie (range,criteria,count_range) 1
Sumif Function 1
SUMIF summing multiple columns 5
SUMIFs across multiple sheets 7
SUMIF function 7

Back
Top