sumif with multiple criterias
 

I have 3 columns, G,H & I, with numbers in each and data periodically gets
added to the bottom of each column. I am trying to set it up so that when the
number 50 appears in column H, it will sum the numbers in column G, starting
from 1 row below the row 50 is in and ending in the row that the last entry
is made at the bottom. I would also like to take the value of the cell in
column I, 1 row below 50, and subtract it from the value in column I in the
row that the last entry was made, then subtract from that, the sum calculated
from column G. I would like this process to be automated so that as new
numbers are added at the bottoms it will automatically make these
calculations. Does anyone know how to make this work?

sumif with multiple criterias
 

Lots of questions about your data:-

number 50 appears in column H,
This only happens once I assume, with no chance of a duplicate 50 somewhere
else in the column?
Also, are there any gaps in your data or is it contiguous, and will that
always be the case?
Also is all your data numeric?

from 1 row below the row 50 is in and ending in the row that the last
entry
is made at the bottom.
Is there any data at all below your table of numbers in G,H or I?

I would also like to take the value of the cell in
column I, 1 row below 50
Is this the same 50, ie the one in Col H or a different one that appears
somewhere in Col I now?

Just to start you off, the first part of your question might be solved by
the use of something like the following:-

=SUM($G:$G)-SUM(OFFSET($G$1,,,MATCH(50,$H:$H,0)))

Need answers to the rest to do any more though.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03/07

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Doug" wrote in message
...
I have 3 columns, G,H & I, with numbers in each and data periodically gets
added to the bottom of each column. I am trying to set it up so that when
the
number 50 appears in column H, it will sum the numbers in column G,
starting
from 1 row below the row 50 is in and ending in the row that the last
entry
is made at the bottom. I would also like to take the value of the cell in
column I, 1 row below 50, and subtract it from the value in column I in
the
row that the last entry was made, then subtract from that, the sum
calculated
from column G. I would like this process to be automated so that as new
numbers are added at the bottoms it will automatically make these
calculations. Does anyone know how to make this work?

sumif with multiple criterias
 

The number 50 in column H appears multiple times and the number of rows
between each 50 is random. In some cases the rows with 50 in Column H are
adjacent to each other and in other cases there are many rows between them. I
would like the formulas to use the last 50 in column H (the most lowest down
the column) as the reference point and then when another 50 is added to
column H the formulas below that would use that 50 as the reference point.
There are no gaps between the rows with data, but below the data are blank
spaces with whatever formulas I end up putting in there since they will
eventually be filled with the calculated data. There are other columns which
I did not mention because they are not used in the calculations; such as
names, catagories and dates, which are chronologically most current at the
bottom row. All of the rows used in the calculations, G,H & I, contain only
numbers, no text. Nothing else is below the data in G,H & I. Column H is the
only column that contains the number 50.

Ken, thank you for your help.
Doug

"Ken Wright" wrote:

Lots of questions about your data:-

number 50 appears in column H,
This only happens once I assume, with no chance of a duplicate 50 somewhere
else in the column?
Also, are there any gaps in your data or is it contiguous, and will that
always be the case?
Also is all your data numeric?

from 1 row below the row 50 is in and ending in the row that the last
entry
is made at the bottom.
Is there any data at all below your table of numbers in G,H or I?

I would also like to take the value of the cell in
column I, 1 row below 50
Is this the same 50, ie the one in Col H or a different one that appears
somewhere in Col I now?

Just to start you off, the first part of your question might be solved by
the use of something like the following:-

=SUM($G:$G)-SUM(OFFSET($G$1,,,MATCH(50,$H:$H,0)))

Need answers to the rest to do any more though.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03/07

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------



"Doug" wrote in message
...
I have 3 columns, G,H & I, with numbers in each and data periodically gets
added to the bottom of each column. I am trying to set it up so that when
the
number 50 appears in column H, it will sum the numbers in column G,
starting
from 1 row below the row 50 is in and ending in the row that the last
entry
is made at the bottom. I would also like to take the value of the cell in
column I, 1 row below 50, and subtract it from the value in column I in
the
row that the last entry was made, then subtract from that, the sum
calculated
from column G. I would like this process to be automated so that as new
numbers are added at the bottoms it will automatically make these
calculations. Does anyone know how to make this work?