• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help creating a conditional format formula?

gnt102

New Member
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.
 

Attachments

  • Template_Outbreak Tracker_Example.xlsx
    12.3 KB · Views: 4
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
 

Attachments

  • Template_Outbreak Tracker_Example-ETAF.xlsx
    13.1 KB · Views: 2
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.
 
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
 
Back
Top