Forum Discussion
Need to highlight group of rows by subtotal; Conditional Formatting Maybe?
I lost a format I used to have in a spreadsheet and can't find the way to re-create it again. It was a long time ago when originally created it. I need to highlight the group of rows which subtotal...
in the conditional formatting formula just lock the column using '$'. so in Riny_van_Eekelen example sheet change =SUM(B$2:B$11)<$F$2 to =SUM($B$2:$B$11)<$F$2 and all the columns in the "Applies To" range will be highlighted (in this case $B$2:$D$11 will highlight cols B:D based on the sum in col B)
In your image if I assume "Amount" is what you want to sum and that is col G then highlight all the columns of interest (e.g. A:M) and then use a formula like:
=Sum($G1:$G10)>100 to highlight all rows where itself + 9 next rows have a sum > 100
note: in the above same if rows 2:9 sum >100 then the header row will also be highlighted so you may want to avoid that using =And(Sum($G1:$G10)>100, row($G1)>1)