I have a datadump into Excel once a month and the number of rows changes
every month. In the datadump are a number of columns. What I am trying to do
is to sum the data in column I based on criteria in column C, which is easy
enough to do using SUMIFS, however what I would like to do is to be able to
set the start row and end row manually. Ie the user dumps the data into
Excel, looks for the row number where the data starts, and the same for
where it ends and then input these two number into a block that the SUMIFS
function would pick up.

So for instance, the data data starts in row 2 and ends in row 48 so the
arrays in the SUMIFS function would be C2:C48 and I2:I48. I would then input
into the Start cell "2" and the End cell "48". What function would I use and
what is the syntax?

There are several function you can use, OFFSET, INDIRECT but the best one to
use
would be INDEX because it is not volatile

=SUMIF(INDEX(C:C,F1):INDEX(C:C,F2),"abc",I:I)

where F1 is start row and F2 end row



--


Regards,


Peo Sjoblom


"Murray" wrote in message
...
I have a datadump into Excel once a month and the number of rows changes
every month. In the datadump are a number of columns. What I am trying to
do is to sum the data in column I based on criteria in column C, which is
easy enough to do using SUMIFS, however what I would like to do is to be
able to set the start row and end row manually. Ie the user dumps the data
into Excel, looks for the row number where the data starts, and the same
for where it ends and then input these two number into a block that the
SUMIFS function would pick up.

So for instance, the data data starts in row 2 and ends in row 48 so the
arrays in the SUMIFS function would be C2:C48 and I2:I48. I would then
input into the Start cell "2" and the End cell "48". What function would I
use and what is the syntax?

Look into the Indirect function, it allows you to put addresses in a cell
that a formula will use, for example if you put C2 in cell A1 and C90 in cell
A2 and this formula in another cell
=SUM(INDIRECT(A1):INDIRECT(A2))
it will sum C2:C90

If you point all of your formulas at A1 and A2 then all you will need to
change is the start cell and end cell.
Hope that makes more sense than it sounds.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Murray" wrote:

I have a datadump into Excel once a month and the number of rows changes
every month. In the datadump are a number of columns. What I am trying to do
is to sum the data in column I based on criteria in column C, which is easy
enough to do using SUMIFS, however what I would like to do is to be able to
set the start row and end row manually. Ie the user dumps the data into
Excel, looks for the row number where the data starts, and the same for
where it ends and then input these two number into a block that the SUMIFS
function would pick up.

So for instance, the data data starts in row 2 and ends in row 48 so the
arrays in the SUMIFS function would be C2:C48 and I2:I48. I would then input
into the Start cell "2" and the End cell "48". What function would I use and
what is the syntax?