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 is less than $$. I keep finding how to highlight just the subtotal, which is easy. But I really want to highlight the group of rows that totalized less than $. This is killing me. Can someone help or have an idea of how to do it? I hope I explained myself.
10 Replies
CDMWK40 Perhaps best demonstrated in a working example. See attached.
Riny_van_Eekelen Thank you! I've bee able to do that. However, what I'm looking for is to highlight not just the cell but the whole row and range of rows were subtotal is less than 2500 for example.
One more thing. If sum is less than say 250, then both group 1 and 2 will be highlighted or only group 2?
HiCDMWK40
Can you give a screenshot of your expectation. Need more understanding of final output expected.
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)
