Conditional Formatting with multiple criteria for first instance only

Cynthtmr

New Member
Joined
Jan 30, 2025
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I'm making a worksheet to show pricing for elements of custom packages.

I have the actual formula for the pricing worked out;

Cell M11:
=IF($K11=1,INDEX($F$5:$F$25,MATCH(1,("x"=$B$5:$B$25)*($I11=$E$5:$E$25),0)),IF($K11>1,MAX(IF(($B$5:$B$25="x")*($E$5:$E$25=$I11),$F$5:$F$25))+10*($K11-1),0))

But I also need to use conditional formatting to show which price is being selected for every part of the package.

First I choose what's included in the package by entering "x" (column B)
If the selections have different methods (column E), then the highest price for that method is charged.
Every subsequent same method is charged +$10 (the formula above is doing this)

For the conditional formatting, I have this formula so far;
=AND($B5="x",$E5=$I$8,$F5=$L$8)

I have this condition entered for each method with $I$8 and $L$8 changed to correspond to the different methods and the highest price of each method.

How do I make it so that only the first instance of a specific combination of criteria is highlighted?

For example cells F7 and F8 have met all the criteria ("x" entered in B, same method in E, match the highest price) but I need the first instance of $29 (Cell F7) to be highlighted, and any secondary instances to highlight the $10 cell instead (cell G8)
Same goes for cells F16 and F18. They're both "x", and both the same method, and same price, but I need the first instance to highlight the "$25", and the second instance to highlight the "$10"

I'm just stuck and appreciate any help given! Thanks!
 

Attachments

  • Excel 1.30.25.jpg
    Excel 1.30.25.jpg
    192.7 KB · Views: 1

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Mini Sheet

Tissue worksheet.xlsx.xlsm
ABCEFGHIJKLMNOP
1Package WorksheetPriceList
2
3
4NameMethodPriceAdd-on Price
5Al totB4.20$29$10Custom Price Method Adjusted: $95.00
6xAsh0$21$10
7xB totB4.20$29$10MethodsTotalSelectedHighest $
8xCa totB4.20$29$1001121$21- First analysis of every method is charged highest pricelist
9Cl extB3.10$29$10B1.10100$0- Secondary analyses of every method is charged +$10
10Cu totB4.20$29$10B2.20100$0
11Fe totB4.20$29$10B3.104225$35
12Mg totB4.20$29$10B3.40100$0
13Mn totB4.20$29$10B4.2013229$39
14MoistB1.10$19$10
15Mo totB4.20$29$10
16xNO3-N extB3.10$25$100B1.10B2.20B3.10B3.40B4.20
17Total NB2.20$25$10FALSEFALSEFALSEFALSEFALSEFALSE
18xPO4-P extB3.10$25$10TRUEFALSEFALSEFALSEFALSEFALSE
19P totB4.20$29$10FALSEFALSEFALSEFALSEFALSETRUE
20K extB3.10$26$10FALSEFALSEFALSEFALSEFALSETRUE
21K totB4.20$29$10FALSEFALSEFALSEFALSEFALSEFALSE
22Na totB4.20$29$10FALSEFALSEFALSEFALSEFALSEFALSE
23S totB4.20$29$10FALSEFALSEFALSEFALSEFALSEFALSE
24S extB3.40$29$10FALSEFALSEFALSEFALSEFALSEFALSE
25Zn totB4.20$29$10FALSEFALSEFALSEFALSEFALSEFALSE
26FALSEFALSEFALSEFALSEFALSEFALSE
27FALSEFALSEFALSEFALSEFALSEFALSE
28FALSEFALSEFALSETRUEFALSEFALSE
29FALSEFALSEFALSEFALSEFALSEFALSE
30FALSEFALSEFALSETRUEFALSEFALSE
31FALSEFALSEFALSEFALSEFALSEFALSE
32FALSEFALSEFALSEFALSEFALSEFALSE
33FALSEFALSEFALSEFALSEFALSEFALSE
34FALSEFALSEFALSEFALSEFALSEFALSE
35FALSEFALSEFALSEFALSEFALSEFALSE
36FALSEFALSEFALSEFALSEFALSEFALSE
37FALSEFALSEFALSEFALSEFALSEFALSE
38
39
Custom
Cell Formulas
RangeFormula
E5:E25E5=VLOOKUP($D5,INDIRECT($M$1&"!$B$7:$G417"),5,FALSE)
F5:F25F5=VLOOKUP($D5,INDIRECT($M$1&"!$B$7:$G417"),6,FALSE)
M5M5=SUM(M8:M13)
I8:I13I8=SORT(UNIQUE(E5:E25,FALSE,FALSE))
J8:J13J8=COUNTIF($E$5:$E$25,$I8)
K8:K13K8=COUNTIFS($B$5:$B$25,"x",$E$5:$E$25,$I8)
L8:L13L8=MAX(IF(($B$5:$B$25="x")*($E$5:$E$25=$I8),$F$5:$F$25,))
M8:M13M8=IF($K8=1,INDEX($F$5:$F$25,MATCH(1,("x"=$B$5:$B$25)*($I8=$E$5:$E$25),0)),IF($K8>1,MAX(IF(($B$5:$B$25="x")*($E$5:$E$25=$I8),$F$5:$F$25))+10*($K8-1),0))
I17:I37I17=AND($B5="x",$E5=$I$8,$F5=$L$8)
J17:J37J17=AND($B5="x",$E5=$I$9,$F5=$L$9)
K17:K37K17=AND($B5="x",$E5=$I$10,$F5=$L$10)
L17:L37L17=AND($B5="x",$E5=$I$11,$F5=$L$11)
M17:M37M17=AND($B5="x",$E5=$I$12,$F5=$L$12)
N17:N37N17=AND($B5="x",$E5=$I$13,$F5=$L$13)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F5:F25Expression=AND($B5="x",$E5=$I$8,$F5=$L$8)textNO
F5:F25Expression=AND($B5="x",$E5=$I$13,$F5=$L$13)textNO
F5:F25Expression=AND($B5="x",$E5=$I$11,$F5=$L$11)textNO
C5:E25Expression=IF($B5="x",TRUE,FALSE)textNO
C6:E25Expression=IF($B6="x",TRUE,FALSE)textNO
Cells with Data Validation
CellAllowCriteria
B5:B25Listx
 
Upvote 0

Forum statistics

Threads
1,226,114
Messages
6,189,052
Members
453,522
Latest member
Seeker2025

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top