Thread: Finding sums using conditionally formatted cells

Hi,

I have been wracking my brains over this one. Attached is a matrix where, for each cell, if the value of its column header (found in cells C2:T2) is greater than or equal to the value of the row header (found in cells B3:B27) above it and less than the value of the row header on the same line as it, the cell is highlighted light green using conditional formatting. How would you calculate the following?

1) for each column, the sum of all the cells above the green highlighted box
2) for each column, the sum of all of the cells below the green highlighted box

I have been able to calculate #1 by using a sum(if) array function but when I apply this to #2 I always get the value included in the box. How do you calculate #2, or barring that, find the value of the conditionally formatted box?

Thanks!

In C28:

Code:

=IF(MATCH(1,INDEX(((C2*100>=$B$2:$B$26)*(C2*100<$B$3:$B$27)),0),0)-1=0,0,SUM(C3:INDEX(C3:C27,MATCH(1,INDEX(((C2*100>=$B$2:$B$26)*(C2*100<$B$3:$B$27)),0),0)-1)))

copied across

in C29:

Code:

=SUM(INDEX(C3:C27,MATCH(1,INDEX(((C2*100>=$B$2:$B$26)*(C2*100<$B$3:$B$27)),0),0)+1):C27)

copied across