Excel 2007 : Count conditioned colour cells

I have looked at a number of web answers but i am not getting the result.

Can anyone advise if there is an easy formula to count the cells which are conditionally formatted to yellow in a range. e.g. in the range there are 20 cells, if 7 are yellow, count = 7.

Thanks in advance

Martin

Simple solution - ape the logic of the conditional format.

Let's assume cells in range (A1:A10) are yellow because their value exceeds ten - to count the yellow cells:

Please Login or Register  to view this content.

obviously the above is a hypothetical example - if you need further assistance post back with the necessary info. re: Conditional Format logic.

Thanks but this wont work. The cells are conditioned by highlighting the lowest integer in a row. The highlighted cells could be any whole number value in the column I am trying to count and there are other whole numbers also which will not be conditioned to Yellow.

The formula needs to actually count by condition colour yellow.

Regards


Martin

If you have logic determining Conditional Formats I would say that more often than not it can be replicated in aggregate using native functions.

If I understand correctly you are trying to determine how many cells in a given column contain the lowest value for their respective rows, correct ?

Using the following hypothetical example...

-- A1:D10 contain numbers

-- CF rule exists that highlights in yellow the MIN value in each row (may/may not have dupes)

-- A12:D12 are to contain a formula counting how many yellow cells occur in rows 1:10 above (ie A12 count of yellow in A1:A10)

The formula we would use to do this would be:

Please Login or Register  to view this content.

edit: uploaded demo attachment of above for proof of concept etc (note XL2007+ assumed)

Have tried the following=SUMPRODUCT((G$3:G$12=SUBTOTAL(5,OFFSET($G$3:$L$3,ROW(G$3:G$12)-ROW(G$3),0)))+0)

Not yet working but heres the table. Have shown the manual count which I need, and the formula count results. Any further advice appreciated and thankyou for your help.

Martin

GHIJKL3 7,500 1.000.500.250.080.060.044 15,000 2.001.000.500.170.130.085 22,500 3.001.500.750.250.190.136 30,000 4.002.001.000.330.250.177 37,500 5.002.501.250.420.310.218 45,000 6.003.001.500.500.380.259 52,500 7.003.501.750.580.440.2910 60,000 8.004.002.000.670.500.3311 67,500 9.004.502.250.750.560.3812 75,000 10.005.002.500.830.630.42Manual Count532000Formula count??0
000010

Martin, best to attach a sample file

Originally Posted by FAQ

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

On an aside - no need to quote prior posts in their entirety - quote where necessary to maintain a logical flow to your thread (ie answering out of sequence).

OK, so the condition is a little more complex in so far as the MIN is conditional (greatest wholly divisible value). This condition is not captured in the SUBTOTAL hence incorrect result.
I will have a think ... I do have a patio to clean so might not be immediate.

Obviously in terms of quick fix - store the MIN result in constant column to right of table and test on that, eg:

Please Login or Register  to view this content.

Then:

Please Login or Register  to view this content.

You could also use M2:M11 as basis for your CF logic - there is a bug with Array logic in Conditional Formatting in XL2007 so this is no bad thing (if that's what you're using)

On a final note... if you want to use VBA you can (link below) but I'd be inclined to persist with formula route outlined

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

I am still looking at this by the way though I believe the aforementioned "helper" column route is certainly worthy of consideration.

It's not clear if the values in the results matrix are being used for anything else - if not you could simply revise the formula in the matrix itself:

Please Login or Register  to view this content.

Your results then become a basic sum of the column given each cell in the matrix contains either 1 or 0
1: header = the greatest multiple of En
0: header <> the greatest multiple of En

You would revise your CF logic such that the rule becomes: =F2

Originally Posted by MC

do you do excel training?

No I'm afraid not.

Originally Posted by MC

This one works.

Glad you have something you're happy to work with.
Bear in mind that Arrays are (generally speaking) "computationally expensive" and as such should be used in moderation.
If you're using en masse you might want/need to consider alternative routes - e.g the helper column route outlined previously (more efficient if less elegant).