Ranking by Conditional Formatting

[Liz]

If I have a spreadsheet where I used colors for conditional formatting, how
would I rank those items. For example

store 1 green yellow yellow green
store 2 yellow green green green

I want to use a formulat that would tell me how many times the green
formatting is in store 1 and 2 and then perform a rank.

 

[Mike H]

Liz,

Because the colour is a result of a conditional format it's not as
straightforward as it may seem and may require quite involved code.

We may be able to devise a formula if you tell us the CF conditions that
cause the cells to change colour.

Mike

 

[Liz]

There are 6 columns of data different conditional formatting based on the
goals below.

1st column 3.5 5.20
2nd column .5 1.00
3rd column 40% 61.54%
4th column 37% 42%
5th column 65% 93.3%
6th column 26% 31.5%

now store 1s results are directly above.

I need to rank multiple store results and I need to rank them by the number
of times they meet the goals. So for example, the store above would meet all
the goals. Does that make sense?

 

[Mike H]

Liz,

That may be enough

=SUMPRODUCT(--(B1:B6>A1:A6))

Lets say the data you gave are in A1 - A6 & B1 - B6
The above formula returns 6 i.e. the number of time column B is > column A

Is that enough or is your actual data layout more complicated.

Mike

 

[Liz]

Let me try and clarify how my spreadsheet is set up

column a column B column c
store name store result goal

the store result and goal repeat 6 times for different criteria. How would I
get the formula to work in that instance?

Elizabeth

 

[Mike H]

Liz,

You add an extra condition to the formula I gave you

SUMPRODUCT((A1:A6="Store 1")*(C1:C6>B1:B6))

In Practice I would use a cell reference

SUMPRODUCT((A1:A6=D1)*(C1:C6>B1:B6))

Where D1 contained the name of the store.

Mike