using a cell format in a countif formula

[cboz]

I have a range that I have used a conditional format to color the 3 lowest values in each row. lowest is green, next lowest is blue and the third lowest is yellow. Now I want to count the number of green cells in a column to find out how many in that column have the lowest value. Is there a way to use the countif function based on the cell color to get this info. or is there a better way to do this?

 

[SkipVought]

Hi,

Use the formulas that you used in your CF criteria.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[byundt]

Here's a function that will return the color of the cell--whether established by regular formatting or conditional formatting. This could be useful if the range you want to count has different conditional formatting formulas in the member cells.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

 

[cboz]

I have applied CF to a range of cells with the following conditions:

Cond 1 =B2=SMALL($B2:$N2,1) Format cell interior green
Cond 2 =B2=SMALL($B2:$N2,2) Format cell interior blue
Cond 3 =B2=SMALL($B2:$N2,3) Format cell interior yellow

This paints the cell with the lowest value in a row in the range green. The second lowest value in a row in the range blue and so forth. The result is that I now have some cells in a column that are green, some in the column are blue, and some are yellow. Now I want to know how many cells in a column contain the lowest value(green). I was wondering if I could use the countif function to get this info.

 

[byundt]

Try a SUMPRODUCT formula instead of COUNTIF:
=SUMPRODUCT(($B2:$N2=SMALL($B2:$N2,1))*1)

 

[SkipVought]

How 'bout
[tt]
=COUNTIF($B2:$N2,SMALL($B2:$N2,1))
=COUNTIF($B2:$N2,SMALL($B2:$N2,2))
=COUNTIF($B2:$N2,SMALL($B2:$N2,3))
[/tt]


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[cboz]

If I manualy count the number of green cells in column B2:B134 I get 12 green cells. How can the formulas you have given me do this. Don't I need to reference the cells in column B to get a count of how many are green in column B?

Both the Sumproduct and CountIF formulas you folks suggest return a value of 1 not 12.

 

[SkipVought]

That's because the SMALL formula, calculates with respect to a SINGLE ROW for determining the shadings in that row.

You have CHANGED the criteria but going vertical. You'll have to use the reference that byundt suggested earlier.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[byundt]

If you don't want to use the VBA function to return the color, you could add a column (let's say column Z) to hold the result of the SMALL function for each row. You could then use:
=SUMPRODUCT((B2:B134=Z2:Z134)*1)

 

[cboz]

That's what I thought. Now how to couun the green cell in range B2:B134.

 

[cboz]

Maybe if I explain where I am trying to go with this. I have a spreadsheet that has a list of items in column A. In row one the names of the suppliers are listed as column headings. Each supplier has quoted a price for each item and this is listed in the columns. In other words, in column B suppler #1 has listed his price for the item in column A. Supplier #2 hs listed his price in column C, and so forth for each suppler through column N. Now I want to know, for each supplier, how many times his quoted price is the lowest. That way I can evaluate which suppler has the greatest number of low bids. The first thing I tried was creating a column to hold the result of the "Small" functions. But this just gives me the lowest value in the row which doesn't help count the number of low bids for a given supplier. Then I came up with the idea to color the cells for the 3 lowest bids in an individual row. This worked great for a visual cue but still now count by supplier. Then I thought maybe I use the countif function based on the color of the cell in the COLUMN for a suppler but I didn't know how so I came to you guys. I know this is a wordy explination but I hope it clarifies what I am trying to achieve. Can it be done? If not I can live with NO.

 

[Zathras]

You can get the data you need by adding a few more columns:

Add a column (say P) to contain =SMALL($B2:$N2,1)
Then add a column (Q) to contain =MATCH(P2,B2:N2,0)
Then add a column (R) to contain the supplier's name with =INDEX($B$1:$N$1,1,Q2)

Finally, do a pivot table report grouping column R and counting item numbers. (Or if you prefer, COUNTIF)

 

[cboz]

Thank you Zathras. Your solution is not what I was hoping for but it does get me the information I need. Thank you all for your patience.

I still wish there was a way to use the format of a cell in a formula. It would have been useful to me in so many ways. Oh well as the Rolling Stones so aptly put it "You don't always get what you want but you get what you need.

I got what I needed and I thank you for that.

 

[byundt]

cboz,
You can use the cell format in a formula. You just need to use a VBA function to return it. See

http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

or

http://www.cpearson.com/excel/CFColors.htm

for code that returns the color.
Brad

 

[Zathras]

cloz, you can encapsulate the worksheet column method in a VBA function:

Function CountLowestCost(ARange As Range, ASupplier As String) As Integer
Dim oRow As Range
Dim nSmall
Dim nOffset As Integer
Dim sSupplier As String
Dim sSupplierParm As String
Dim nCount As Integer

  sSupplierParm = UCase(ASupplier)
  For Each oRow In Intersect(ARange, ARange.Offset(1, 0)).Rows
    nSmall = WorksheetFunction.Small(oRow, 1)
    nOffset = WorksheetFunction.Match(nSmall, oRow, 0)
    sSupplier = WorksheetFunction.Index(ARange, 1, nOffset)
    If UCase(sSupplier) = sSupplierParm Then
      nCount = nCount + 1
    End If
  Next oRow
  CountLowestCost = nCount
End Function

Assuming your data are in a normal table form, then after putting that function into a code module, you can use it like this:
Assuming the data are in B1:N134 with row 1 containing the supplier's names and B2:N134 the various prices, then you can put this formula in B136 and copy it into C136:N136 to see the results you want:

[b]
 =CountLowestCost($B$1:$N$134,B$1)

[/b]