Home

conditional formatting of column based on text in that column AND another column

%3CLINGO-SUB%20id%3D%22lingo-sub-294188%22%20slang%3D%22en-US%22%3Econditional%20formatting%20of%20column%20based%20on%20text%20in%20that%20column%20AND%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294188%22%20slang%3D%22en-US%22%3E%3CP%3EI%20keep%20a%20sales%20log%26nbsp%3B%20updated%20daily%20with%20stock%20numbers%20of%20sold%20vehicles.%20How%20would%20I%20format%20the%20cells%20in%20column%20D%20to%20be%20highlighted%20if%20the%20stock%20number%20starts%20with%20%22R%22%20AND%20the%20value%20in%20column%20B%20is%20%22N%22%3F%20And%20then%20how%20would%20I%20set%20a%20cell%20elsewhere%20to%20count%20those%20highlighted%20cells%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-294188%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294384%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20of%20column%20based%20on%20text%20in%20that%20column%20AND%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294384%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20range%20starts%20from%20row%201%20you%20may%20apply%20conditional%20formatting%20rule%20with%20formula%3C%2FP%3E%0A%3CPRE%3E%3D(LEFT(%24D1%2C1)%3D%22R%22)*(%24B1%3D%22N%22)%3C%2FPRE%3E%0A%3CP%3ETo%20count%20such%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((LEFT(%24D1%3A%24D2000%2C1)%3D%22R%22)*(%24B1%3A%24B2000%3D%22N%22))%3C%2FPRE%3E%0A%3CP%3Eor%20what%20is%20your%20range%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

I keep a sales log  updated daily with stock numbers of sold vehicles. How would I format the cells in column D to be highlighted if the stock number starts with "R" AND the value in column B is "N"? And then how would I set a cell elsewhere to count those highlighted cells?

1 Reply
Highlighted

If you range starts from row 1 you may apply conditional formatting rule with formula

=(LEFT($D1,1)="R")*($B1="N")

To count such

=SUMPRODUCT((LEFT($D1:$D2000,1)="R")*($B1:$B2000="N"))

or what is your range

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies