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.


Comments

Post a Comment

Popular posts from this blog

javascript - AngularJS custom datepicker directive -

i make custom datepicker directive custom template. but have no idea how start building it... how include date data directive? i appreciate guide or give me advice work on more precisely. it might you! follow below steps html code sample: <label>birth date</label> <input type="text" ng-model="birthdate" date-options="dateoptions" custom-datepicker/> <hr/> <pre>birthdate = {{birthdate}}</pre> <script type="text/ng-template" id="custom-datepicker.html"> <div class="enhanced-datepicker"> <div class="proxied-field-wrap"> <input type="text" ui-date-format="yy-mm-dd" ng-model="ngmodel" ui-date="dateoptions"/> </div> <label> <button class="btn" type="button"><i class="icon-calendar"></i>
Read more

javascript - jQuery date picker - Disable dates after the selection from the first date picker -

<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <title>jquery ui datepicker - default functionality</title> <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css"> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script> <link rel="stylesheet" href="/resources/demos/style.css"> <script> $(function () { $("#datepicker1").datepicker(); }); </script> <script> $(function () { $("#datepicker2").datepicker(); }); </script> </head> <body> <p>date 1: <input type="text" id="datepicker1"></p> <p>date 2: <input type="t
Read more