I'm tracking illnesses at my school for frequency & potential outbreaks. I have column E as "Illness Type" and column G as "Teacher." If any teacher has 3+ cases of the same sickness type in their class, I'd like the cells to highlight (bonus points if it highlights the entire rows of the relevant cases). I don't want the cells to highlight just if "GI" (etc.) appears in column E 3+ times, nor if teacher "Jones" is listed 3+ times, but rather if there are 3+ cases of GI illness specifically within Jones' class. Can anyone help? I've spent hours researching online but my Excel skills are so basic, I just don't have the skill set. Maybe it's not even possible? Thanks in advance for any help.
Help creating a conditional format formula?
- Thread starter gnt102
- Start date
ETAF
Active Member
how many illnesses are there ? , if you want different colours for different colours
for the same colour then
you could do a countifs()
=COUNTIFS($E2:$E200,$E2,$G2:$G200,$G2)>=3
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting
Highlight applicable range >>
A2:H1000 - Change, reduce or extend the rows to meet your data range of rows
Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=COUNTIFS($E2:$E200,$E2,$G2:$G200,$G2)>=3
Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
for the same colour then
you could do a countifs()
=COUNTIFS($E2:$E200,$E2,$G2:$G200,$G2)>=3
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting
Highlight applicable range >>
A2:H1000 - Change, reduce or extend the rows to meet your data range of rows
Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=COUNTIFS($E2:$E200,$E2,$G2:$G200,$G2)>=3
Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
Hi, thanks for the help! I currently have 5 illnesses listed (Strep throat, COVID, GI, Flu-like, & other) but more may be added in the future. I tried your formula, and an almost identical one suggested by a colleague ("=COUNTIFS($E$2:$E$200,$E2,$G$2:$G$200,$G2)>=3"), but neither work-- she says it may be because the spreadsheet is too large.
ETAF
Active Member
200 rows is nothing
how many rows do you have on the spreadsheet
is the template you posted just an section of the real spreadsheet - or is the real one different
I have used just column E:E, G:G - which depending on versions is millions of rows
BUT that can slow a spreadsheet down
what happens if you just put the formula into a spare column and copy down
=COUNTIFS($E$2:$E$200,$E2,$G$2:$G$200,$G2)
It should give you a count , by the illness and name
Why the " and (
they are NOT needed
why are they there
i asked for the number of illnesses as i thought you may want them different colours
how many rows do you have on the spreadsheet
is the template you posted just an section of the real spreadsheet - or is the real one different
I have used just column E:E, G:G - which depending on versions is millions of rows
BUT that can slow a spreadsheet down
what happens if you just put the formula into a spare column and copy down
=COUNTIFS($E$2:$E$200,$E2,$G$2:$G$200,$G2)
It should give you a count , by the illness and name
Why the " and (
they are NOT needed
why are they there
i asked for the number of illnesses as i thought you may want them different colours