Count highlighted and text specific cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1127801%22%20slang%3D%22en-US%22%3ECount%20highlighted%20and%20text%20specific%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1127801%22%20slang%3D%22en-US%22%3E%3CP%3EThere%20are%20three%20texts%20possible%20(OT%2C%20PT%2C%20ST)%2C%20and%20highlighted%20when%20something%20is%20complete.%20Each%20patient%20will%20have%20at%20least%20one%20of%20the%20text%20above%20(already%20inputted%20as%20needed)%20and%20when%20the%20evaluation%20is%20complete%20then%20I%20manually%20change%20the%20background%20color%20to%20yellow.%20I%20am%20trying%20to%20automate%20a%20count%20of%20how%20many%20PT's%20are%20highlighted%20in%20yellow.%20I%20am%20familiar%20with%20counting%20text.%20I%20am%20somewhat%20familiar%20with%20counting%20highlighted%20cells.%20I%20cannot%20figure%20out%20how%20to%20do%20both%20simultaneously.%20Thanks%20in%20advance%20for%26nbsp%3Byour%20assistance.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20227px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F167087i2CDFB00EA062E0AE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Table.jpg%22%20title%3D%22Table.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1127801%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1127857%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20highlighted%20and%20text%20specific%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1127857%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531608%22%20target%3D%22_blank%22%3E%40KarenBBM5515%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20%22borrowed%22%20a%20user%20defined%20function%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.exceltip.com%2Ftips%2Fhow-to-get-color-of-the-cell-using-vba-in-microsoft-excel-2010.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.exceltip.com%2Ftips%2Fhow-to-get-color-of-the-cell-using-vba-in-microsoft-excel-2010.html%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20allows%20you%20to%20read%20out%20the%20background%20colour%20of%20a%20cell.%20On%20my%20Mac%2C%20yellow%20has%20the%20value%20of%206.%20I%20you%20can%20live%20with%20adding%20a%20(perhaps%20hidden)%20column%20that%20checks%20if%20the%20cell%20colour%20value%20is%206%2C%20then%20you%20can%20use%20a%20formula%20like%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT(B1%3AB7*(A1%3AA7%3D%22PT%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E.....%20to%20count%20the%20number%20PT%20with%20a%20yellow%20background%2C%20as%20demonstrated%20in%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

There are three texts possible (OT, PT, ST), and highlighted when something is complete. Each patient will have at least one of the text above (already inputted as needed) and when the evaluation is complete then I manually change the background color to yellow. I am trying to automate a count of how many PT's are highlighted in yellow. I am familiar with counting text. I am somewhat familiar with counting highlighted cells. I cannot figure out how to do both simultaneously. Thanks in advance for your assistance. 

Table.jpg

1 Reply
Highlighted

@KarenBBM5515 

I "borrowed" a user defined function from https://www.exceltip.com/tips/how-to-get-color-of-the-cell-using-vba-in-microsoft-excel-2010.html 

It allows you to read out the background colour of a cell. On my Mac, yellow has the value of 6. I you can live with adding a (perhaps hidden) column that checks if the cell colour value is 6, then you can use a formula like:

=SUMPRODUCT(B1:B7*(A1:A7="PT"))

..... to count the number PT with a yellow background, as demonstrated in the attached file.