Excel Forum

Welcome to the Microsoft Excel Forum. Excel is one of the more popular topics here at Computer Learning Zone. This forum is for everyone to read but only students (paid customers) can post here. If you'd like to become a student, you can watch Excel Beginner Level 1 for free, and then purchase Level 2 for just $1.00 here. After you create a logon, you will have full access to the Forums.

Subscribe to this forum and receive an email update whenever new posts are added. You will also be notified by email if any conversations you are involved in receieve new comments.

Click Here to Post a comment and start a new discussion

Please read the FORUM RULES before posting.

Count Cells by Color Upload Images Link
Lisa Ker
14 days ago

Hi All
Happy New Year to everyone!
I have an Excel Sheet where I use conditional formatting to change the colour of a row of cells depending on the values of other cells in the Worksheet.
Now I want to count those cells based on their colour. Read More...

Adam Schwanz
14 days ago

Why count the color? Why not just count the fields with the criteria that is the same criteria that sets a color?

If Red is a number >5 then just count the numbers >5 instead of counting how many reds.

Adam Schwanz
14 days ago

Could you share an image? Maybe I'm misunderstanding what you're doing. Snip & Sketch

Show 13 More Replies...

Lisa Ker
14 days ago

Hi Adam.

Thanks for the quick reply.

So what I have is a list of PRs (purchase requisitions).
The PR number's colour changes depending on whether there is an Order raised (it goes blue), whether the goods are received (it goes yellow) or if we have received the supplier invoice (it goes orange).

I want to know the following:
Total count of Open PRs
Count of PRs with Orders Placed
Count of PRs with goods received
Count of PRs with invoices received

In other words I have a total of 100 PRs. 50 have had orders placed, 20 are open, 10 are goods received and 20 have had their invoices received.

So I can't just count the field that uses the conditional format because some of them will have met all the criteria so will be counted 3 x under Order Placed, GRN and Invoice received.

Not sure if I am making sense?

Lisa Ker
14 days ago

Scott Axton
14 days ago

Based on the picture it appears to me that your color is using the status field (Column) to determine the color.
Correct?

Even though your PR may have gone through many steps, a PR can only have one current status. True?

You need to have 4 counters based on your description above.
Opn, Ord, Gds, and Inv.

If Status = "Open" then
   Opn = Opn + 1
End If

If Status = "Order" then
   Ord = Ord + 1
End If

If Status = "Goods" then
   Gds = Gds + 1
End If

If Status = "Invoice" then
   Inv = Inv + 1
End If

So using the screenshot Ord = 3 and  Inv = 2
Is that what you want?

Richard Rost
14 days ago

Hi Lisa. One of my other students just asked me about this a few days ago. I've got some VBA code to count cells by their color. I'll put it together in a Fast Tip video. Haven't done one for Excel in a while. Stay tuned...

Richard Rost
14 days ago

Here ya go... Excel Count by Color

Lisa Ker
12 days ago

@ Scott Axton

You have it exactly right.

Lisa Ker
12 days ago

@Richard Rost

You guys are amazing and so fast!

I don't know what I'm doing wrong, but it simply counts all the cells and not the cell by colour.

Richard Rost
12 days ago

Well, let's see your code.

Lisa Ker
12 days ago

Richard Rost
12 days ago

Yeah, code looks good. Let's see the sheet with the formula in the formula bar.

Lisa Ker
12 days ago

@Richard Rost
The cell location where the Sample Colours are is on another sheet in the workbook called "Sample"
Also, the top area of the range will always be A10 but the bottom will keep increasing which is why no $ at the second cell range number.

Richard Rost
11 days ago

Yeah, neither of those two things should matter. Try putting them together on one sheet and see what happens. MsgBox the color values and make sure they match. I'd really have to see your workbook file in order to tell you for sure.

Excel Expert Charts Upload Images Link
Carol Herriot
33 days ago

Can you tell me please which of the Excel Expert classes expands on charts and graphs? I have taken all of the beginner classes and would like to learn more about charts. Thank you.

Richard Rost
33 days ago

Hi Carol. Excel Beginner 4 covers most of the charting material. Was there something specific you were looking for beyond that?

Carol Herriot
33 days ago

Ummm '. I seem to remember you saying that you are only covering the basics in the Beginner series and will cover
more about charts eg formatting, other styles of charts etc in the expert classes.

Maybe I am confused?

Show 2 More Replies...

Excel Expert Classes Upload Images Link
Carol Herriot
45 days ago

I want to learn how to make excel automatically enter specific data from one spreadsheet to another spreadsheet. Which course(s) should I take to learn this? I have already taken all of the Beginner classes.

Alex Hedley
45 days ago

VLOOKUP?

Kevin Robertson
45 days ago

Is the data in the same Workbook or a different Workbook?

Show 4 More Replies...

Show Older Comments...
View in Table Format