Making Conditional Formatting for a SUMIF filter in Google Sheets relative? -

i have handy little google sheet formula applies color given summed total can't formula relative. suspect either easy genius fix or impossible in sheets. help?

sorry can't post images, here's rough simulation of table a1 - b4

• john doe | 25%
• martha roe | 25%
• john doe | 75%
• jane doe | 25%

in table above, i'm using conditional formatting "format cells if..custom formula is..." , formula sums values in column b john roe , martha roe (column a) , turns both name , value cell red if value exceeds 100%. use formula below:

=sum(filter(b$1:$b4,a$1:a$4 = a1)) >= 100% 

(full credit spreadsheetpro.net's article intro sumifs, countifs, & averageifs functions in google spreadsheet.)

unfortunately, when use conditional formatting approach , drag formula next cell in row, still thinks mean a1 (not a2); it's applying original formula multiple rows. right need manually change formula each cell. maddening.

what i'd have update relative cell it's in. make easier, i'd ok if there way tell in formula "use cell i'm in" (instead of specifying a1).

1. is there way can fix current formula google knows a1is relative? or, barring that
2. is there way use conditioning formatting formula knows use current cell in formula?

thanks!

the trick not drag formula per se, apply range require. formula propagated across range, respecting absolute , relative references if dragging in-cell spreadsheet formula on range.

in case, think need apply:

=sum(filter($b$1:$b$4,$a$1:$a$4=$a1))>=100%

to range a1:b4.

it can conceptualise going on plugging formula spare spreadsheet cell, , dragging on 2x4 range of cells.