image of spreadsheet

I have a spreadsheet, and there are various drop-down menus on it to select various criteria.

When certain criteria is selected then data in different cells show up in various colours. I have used conditional formatting for this.

I now need to be able to auto-count the data in accordance to its colour.

So, for example:

  • rows 1, 3, 4, and 7 are green
  • rows 2 and 6 are blue
  • row 5 is yellow

How do I count green rows, blue rows and yellow rows individually, and display the numbers in a different cell? Remembering that these rows are default white, and only change to their colours when relevant criteria is selected from the drop down boxes.

Have tried (for example) =IF($A1:$A9)='White' but I'm not sure how to format it to reflect the colour of the text, rather than the text itself.

  • what software do you use? Independently whether colour is set by conditional formatting or not, Excel doesn't have formula to check it. You either need to check the original conditions, either use macro for that. – Máté Juhász Nov 23 '16 at 12:27
  • Microsoft Excel... so basically this can't be done without Excel creating a formula to check it? – L Storer Nov 23 '16 at 12:30

To check color of cell formatting would require VBA. That can be rather difficult if you are not familiar with VBA. As an alternate, you can use a formula to count the cells that meet the same criteria you used for Conditional Formatting.

For instance, if formatting when the value is a single value like =20 or <20 you can use COUNTIF to count the instances of that criteria being met.

=COUNTIF($A1:$A9, "<20")

If you are formatting a cell with a range value, lets say 40 to 49, you can use COUNTIFS to check that the cell value is equal to or greater than 40 AND equal to or less than 49. This will count the instances within the range that are between 40 and 49.

=COUNTIFS($A1:$A9, ">= 40", $A1:$A9, "<= 49")

I created an example below by using conditional formatting in A1:A9, then used these formulas to count the same conditions. Obviously it is not laid out the same as your sheet, but it should give you an idea how to do this.

enter image description here

  • hi charlie, thank you for your help. the problem i have is that the values in the many different cells on my sheet are duplicated here and there. So using my example sheet, i could say that i want a 75mile journey, with up to 35minutes break. All rows, except the top row, should change colour because they no longer apply. (all over 35minutes) So the count will equal 1, as only one row is in colour. – L Storer Nov 23 '16 at 15:18
  • 1
    @LStorer: I can’t tell whether you’re saying, “Thank you, that solved my problem” or “Thank you, but that doesn’t solve my problem, because ...”  You’re giving us an example of “75 miles” and “35 minutes”, but these values are meaningless / useless to us since we have no idea what your conditional formats are doing.  And we shouldn’t need to know. … (Cont’d) – Scott Nov 23 '16 at 19:17
  • 1
    (Cont’d) … CharlieRB, and Máté Juhász before him, are trying to explain that you need to take the formulas that you are using in your conditional formats and use those same formulas in the cells where you want the counts.  Do you have a problem doing that?  If so, why?  Please do not respond in comments; edit your question to make it clearer and more complete. – Scott Nov 23 '16 at 19:17
  • Firstly.... there is no need to be so rude. I was being polite in saying thank you for helping. if you cant see 75 and miles in my example sheet at the top of this, then i will try and repost it. Where do you suggest i reply to comments @Scott ? if i alter the question then how do others (like you) who look at it, know what my original problem is in order to make suggestions? I apologise that i don't speak 'Excel', but i am trying to!!!!!! so understanding Charlies response was a little tricky! – L Storer Nov 24 '16 at 10:55
  • 1
    I did not realize there was an additional condition. My not knowing what criteria you are using for your conditional formatting makes it difficult for me to be able to give an exact answer. Knowing how you applied the conditional formatting would be very helpful to answering accurately. It is perfectly acceptable (and encouraged) to edit your question to add details. Lastly, if there is an additional condition to be checked, add it to the formula; =COUNTIFS($A1:$A9, ">= 40", $A1:$A9, "<= 49", $C1:$C9, "=35"). Once all conditions are met, it will count it. – CharlieRB Nov 24 '16 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.