View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GBExcel GBExcel is offline
external usenet poster
 
Posts: 5
Default Formula until condition met

How can I SUMIF subject to each successive previous cell's value being less
than the value of the former cell?

I'm using a COUNTIF formula to determine the number of entries in each column.
The table looks something like this:

V2A: 10 20 5 10 5 10 where "V2A" stands for "Value to Add."
CI: 2 6 4 1 3 5 where "CI:" stands for "COUNT IF" values.

R1: x x x x x x where "R" stands for "ROW" as in "ROW 1:"
R2: x x x - x x where "x" stands for a value in a cell.
R3: - x x - x x where "-" stands for an empty cell.
R4: - x x - - x
R5: - x - - - x
R6: - x - - - -
Res: 0 10 0 0 10 20 where "Res:" stands for "Results."

Explanation:
I am looking for a formula that will calculate the results row, (the "Res:"
row). I have 31 columns, so nested IF statements probably won't work.

I used COUNTIF to determine the values to the right of CI. The formula that I
need must SUM the V2A values if the CI values to the columns in coluleft are
lower thanmns to the right. Therefore, the result in column 1 is '0' because
there is no value to the left of the column. The result in column 2 is 10,
because its CI value of 6 is greater than the value of 2 from the column to
its left. The results of columns 3 and 4 are 0 because the columns to their
left are greater. Notice that since the 2nd column has a greater value than
those to its left, (column 1), column 1's value is not SUMmed in formulas to
the right of column 2. The result in column 5 is 10, because the row to the
left has a lesser CI value. The result for column 6 is 20, because it is
greater than the previous 3 columns CI values, but less than column 2's CI
value.

This problem has been driving me nuts! Any help would be appreciated.