Conditional Formatting

Try using the CF formulas in a couple of COUNTIF formulas.

Broadly speaking, no, there are not any functions that do that. Functions look at what is "inside" the cell, and formatting is applied "on top" of them.

Instead what you're better off doing is just running a COUNTIF on the range of formatted cells with the same criteria you used to apply the CF in the first place.

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

Please try the attachment again.

It looks like your CF is kind of a mess, but aren't you just highlighting the lowest & highest values in each row?

In which case, wouldn't the number of green/red cells each be simply be the number of rows you have filled in, with possibly some error handling for rows with one entry?

Named ranges used:
MaxAndMins =Test!$K$3:$L$35
MaxVals =Test!$K$3:$K$35
MinVals =Test!$L$3:$L$35
supplier_1 =Test!$D$3:$D$35
supplier_2 =Test!$E$3:$E$35
supplier_3 =Test!$F$3:$F$35
supplier_4 =Test!$G$3:$G$35
supplier_5 =Test!$H$3:$H$35

Each row of the sample data has a max and a min value. Use two helper columns to extract the max and mins for each row, then use

Please Login or Register  to view this content.

M4:V4 supplier names referring to ranges
ISEVEN(COLUMN())+1) calculates the column of the max/min table/array to use

---

Modify, reduce your conditional format formulas (choose four colors)
highlight min/max for each column:

=D3=MIN(INDEX($D$3:$H$35,,COLUMN(A1)))
=D3=MAX(INDEX($D$3:$H$35,,COLUMN(A1)))

highlight min/max for each ROW:
=D3=MIN(INDEX($D$3:$H$35,ROW(A1),))
=D3=MAX(INDEX($D$3:$H$35,ROW(A1),))

Is this double posted here: https://www.excelforum.com/excel-gen...sum-total.html ??

Perhaps the following will be of some help.
Populate AJ42:DA42 using: =COUNTIFS(AJ3:AJ35,MIN(AJ3:AJ35))
Get a sum of the values in AJ42:DA42 that a visible using: =SUMIFS(AJ42:DA42,AJ2:DA2,"Calc Annual Value*")
Let us know if you have any questions.

Two column Named ranges for annual calcs for each supplier:
Supplier_A ='Pricing Results Analysis'!$V$3:$W$36
Supplier_B ='Pricing Results Analysis'!$AJ$3:$AK$36
Supplier_C ='Pricing Results Analysis'!$AX$3:$AY$36
Supplier_D ='Pricing Results Analysis'!$BL$3:$BM$36
Supplier_E ='Pricing Results Analysis'!$BZ$3:$CA$36
Supplier_F ='Pricing Results Analysis'!$CN$3:$CO$36
Supplier_G ='Pricing Results Analysis'!$DA$3:$DB$36

Two column named range for extracted max/mins:
MaxAndMins ='Pricing Results Analysis'!$DD$3:$DE$36

There are two summary tables: "Multiple Locations" and "Single Locations".

Counts are tallied with:

Please Login or Register  to view this content.

INDEX(INDIRECT(DG$3) points to the supplier's named range
MATCH($DG$2,{"multiple","single"},0) determines which column, Multi or single

* Unfortunately, I was not able to test very well due to so many formulas that referred to to empty cells. There are several cells with formulas different from the other formulas in the same column and several circular ref errors.