SOLVED

Using Conditional Formatting with two types of data

%3CLINGO-SUB%20id%3D%22lingo-sub-1788530%22%20slang%3D%22en-US%22%3EUsing%20Conditional%20Formatting%20with%20two%20types%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788530%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20come%20up%20with%20a%20formula%20to%20highlight%20a%20cell%20when%20it%20is%20less%20than%20a%20number%20and%20column%20F%20%3D%20Service%20B.%26nbsp%3B%20I%20have%20tried%20this%20formula%20%22%3DAND(%24F%3A%24F%3D%22Service%20B%22%2C%24E%3A%24E%2C30000)%22%20but%20it%20does%20not%20work.%26nbsp%3B%20I%20am%20at%20a%20loss.%26nbsp%3B%20I%20need%20to%20highlight%20numbers%20in%20column%20E%20that%20are%20less%20than%2027%2C000%20but%20greater%20than%2033%2C000%20and%20also%20have%20the%20word%20Service%20B%20in%20column%20F.%26nbsp%3B%20I%20also%20need%20to%20do%20the%20same%20for%20Service%20A%20but%20at%20smaller%20values%2C%20which%20once%20I%20get%20the%20formula%20correct%20for%20service%20B%20I%20should%20have%20no%20problem%20creating%20one%20for%20service%20A.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1788530%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1788566%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Conditional%20Formatting%20with%20two%20types%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788566%22%20slang%3D%22en-US%22%3E*correction*%20The%20formula%20I%20am%20currently%20using%20is%20%3DAND(%24F%3A%24F%3D%22Service%20B%22%2C%24E%3A%24E%26lt%3B30000).%20I%20realized%20the%20one%20above%20does%20not%20have%20%26lt%3B.%20I%20also%20realize%20I%20am%20only%20using%20%26lt%3B%20and%20not%20%26gt%3B%20as%20well.%20I%20was%20just%20trying%20to%20get%20the%20formula%20to%20work%20before%20I%20added%20the%20%26gt%3B.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1788570%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Conditional%20Formatting%20with%20two%20types%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788570%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F835130%22%20target%3D%22_blank%22%3E%40TheBoss86%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20trying%20to%20do%20this%20to%20a%20Table%2C%20then%20you%20have%20to%20do%20it%20like%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(AND(%24F2%3D%22Service%20B%22%2C%24E2%26lt%3B30000)%2C1%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BThen%20for%20%22Applies%20to%22%2C%20enter%20the%20range%20you%20want%20it%20to%20happen%20to.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_5bc1d99c.png%22%20style%3D%22width%3A%20881px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227161i0989F58C4B5AA3D3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Snag_5bc1d99c.png%22%20alt%3D%22Snag_5bc1d99c.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1788575%22%20slang%3D%22de-DE%22%3ESubject%3A%20Using%20Conditional%20Formatting%20with%20two%20types%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788575%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F835130%22%20target%3D%22_blank%22%3E%40TheBoss86%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHere%20is%20an%20example%20file%20with%20two%20different%20examples.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1788722%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Conditional%20Formatting%20with%20two%20types%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788722%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727575%22%20target%3D%22_blank%22%3E%40Erut313%3C%2FA%3E%26nbsp%3BThat%20worked%2C%20thank%20you.%26nbsp%3B%20What%20is%20the%20reason%20for%20putting%20the%201%2C0%20at%20the%20end%20of%20the%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1788924%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Conditional%20Formatting%20with%20two%20types%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788924%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727575%22%20target%3D%22_blank%22%3E%40Erut313%3C%2FA%3E%26nbsp%3BI%20thought%20it%20worked%20but%20I%20am%20having%20issues%20now.%26nbsp%3B%20I%20changed%20your%20formula%20to%20this%2C%20so%20it%20would%20pick%20up%20anything%20greater%20than%2033%2C000.%26nbsp%3B%20If%20you%20look%20at%20the%20screenshot%2C%20row%2011%20is%20in%20red%20although%20column%20f%20says%20Service%20A%20and%20the%20value%20is%20not%20greater%20than%2033000.%26nbsp%3B%20Row%2012%20column%20E%20is%20not%20in%20red%20even%20though%20column%20f%20says%20Service%20B%20and%20the%20value%20is%20greater%20than%2033%2C000.%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1788998%22%20slang%3D%22de-DE%22%3ESubject%3A%20Using%20Conditional%20Formatting%20with%20two%20types%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788998%22%20slang%3D%22de-DE%22%3E%3DAND((A11%26gt%3B33000)%2C%20(E12%26lt%3B33000))%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I am trying to come up with a formula to highlight a cell when it is less than a number and column F = Service B.  I have tried this formula "=AND($F:$F="Service B",$E:$E,30000)" but it does not work.  I am at a loss.  I need to highlight numbers in column E that are less than 27,000 but greater than 33,000 and also have the word Service B in column F.  I also need to do the same for Service A but at smaller values, which once I get the formula correct for service B I should have no problem creating one for service A.

12 Replies
Highlighted
*correction* The formula I am currently using is =AND($F:$F="Service B",$E:$E<30000). I realized the one above does not have <. I also realize I am only using < and not > as well. I was just trying to get the formula to work before I added the >.
Highlighted

@TheBoss86 

 

If you are trying to do this to a Table, then you have to do it like this. 

 

 

=IF(AND($F2="Service B",$E2<30000),1,0)

 

 

 Then for "Applies to", enter the range you want it to happen to.

Snag_5bc1d99c.png

Highlighted

@TheBoss86 

 

Here is an example file with two different examples.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Highlighted

@Erut313 That worked, thank you.  What is the reason for putting the 1,0 at the end of the formula?

Highlighted

@Erut313 I thought it worked but I am having issues now.  I changed your formula to this, so it would pick up anything greater than 33,000.  If you look at the screenshot, row 11 is in red although column f says Service A and the value is not greater than 33000.  Row 12 column E is not in red even though column f says Service B and the value is greater than 33,000.   

Highlighted
=AND((A11>33000),(E12<33000))
Highlighted

@TheBoss86 The 1 and 0 are to activate the conditional formatting. Please send me a screenshot of the values that you chose. Sometimes the Formula will get messed up and instead of $F2 it will say something like $F124034, Make sure that doesnt say that.

Highlighted

@Erut313The screenshot I attached has the formula that is being used.   

Highlighted

@TheBoss86 

 

Take a screenshot like mine, so I can see your "Applies To"

Highlighted

@Erut313 Here is what I got.

Highlighted
Best Response confirmed by TheBoss86 (Occasional Contributor)
Solution

@TheBoss86 Here you go. I have it as less than, not greater than.

Highlighted
Thank you for the help!!!!! Got it to work.