CODEWITHSUNDEEP
arraysexcelsumrangecountif
Dave M
Dave M

Reputation: 51

Excel COUNTIF with variable criteria, then adding all results to cells above them

Various formulas were used to create several columns of numbers.

The sums of column B and column C values created the values in column D. The column B numbers are in descending order from B2 to B58. Because those in C are not descending, some of the D values are not as well.

Sample:
B C D
79.3 2.5 81.8
76.7 1.5 78.2
75.3 2.1 77.4
73.2 6.3 79.5*
66.9 1.6 68.5
65.2 0.6 65.8
64.6 1.7 66.3*
62.9 2.2 65.1

In this sample, two values in D come out bigger than the cells above them.

In column E, I would like to find all cells like this in column D where D(n+1) > D(n). Wherever that happens, I'd like to add the adjacent C(n+1) to the entire range of D$2:D(n+1) cells.

Simply put, if D34 > D33, then I'd like to add C34 to all cells above that, in D$2:D33. Ideas?

Upvotes: 1

Views: 711

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34380

I think this will do it :-

=D2+SUM((D3:D$9>D2:D$8)*C3:C$9)

to be entered in E2 using Ctrl Shift Enter and pulled down, assuming that the original data starts in row 2.

The idea is that the sum of all the values in column C where the corresponding values in D below the current cell are out of order is added to the current value in column D.

enter image description here

EDIT

The formula will give incorrect results in the last row of column E if the last pair of numbers in column D are out of order (because it tries to compare D$9:D10 with D8:D$9 in the example). If we can assume that the data is followed by an empty row, this can be fixed easily

=D2+IF(D3<>"",SUM((D3:D$9>D2:D$8)*C3:C$9))

On the other hand, if we can only assume that D$2:D$9 is a fixed range, the formula should be

=D2+IF(ROWS(D$2:D2)<ROWS(D$2:D$9),SUM((D3:D$9>D2:D$8)*C3:C$9))

Thanks to @Dave M for pointing this out.

Upvotes: 1

Related Questions