Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The countif function has a criteria argument – how do I specify a criteria based on the formatting of cells?

I have a list of math homework questions containing per-page the questions numbers (e.g. on the row for page 12 the row will contain the numbers 17 to 32, each in its own cell), and I want to mark special meanings using formatting: Hard questions will be bold, mandatory ones will have yellow background and, of course, the cells containing the numbers of tough and mandatory questions will be bold with yellow background.

Now I want to count the questions and their classes: Say 16 questions on this page, of which 5 are hard but optional, 3 are mandatory but easy, and 2 are mandatory and hard?

Of course, I could add three more rows per page: One would serve as "Hard" flag and one would serve as "Mandatory" flag. The third would be 1 if both the hard and mandatory flags are on. But I want the Excel sheet to be visually pleasing, with no such clutter, and adding three more rows per homework page is ugly and non-intuitive.

Thanks! Avi

P.S. Alternaitve methods for solving this problem will be great. Maybe I'm barking on the wrong tree..

share|improve this question
up vote 2 down vote accepted

As far as I know, COUNTIF only accepts cell references, numbers, strings and wildcards (anything that pertains to the values inside cells, not formats). Here are a few things you can try though.

Option 1: Data Filter & SUBTOTAL

You can use Data Filters along with SUBTOTAL, as in the example below. However, this only works with background colors and not font weights. Instead of using bold font for Hard questions you can perhaps use shades of yellow and red. On the down side, you'd have to manually apply the filter every time you want to count your questions.

enter image description here

Option 2: Custom Function

Public Function COUNTF(w As Boolean, h As Boolean, rng As Range) As Integer

Dim cHard, cMand, cBoth As Integer

For Each c In rng
    If c.Font.Bold Then cHard = cHard + 1
    If c.Interior.Color = RGB(255, 255, 0) Then cMand = cMand + 1
    If c.Font.Bold And c.Interior.Color = RGB(255, 255, 0) Then cBoth = cBoth + 1
Next c

If w And Not (h) Then
    COUNTF = cHard
ElseIf Not (w) And h Then
    COUNTF = cMand
ElseIf w And h Then
    COUNTF = cBoth
    COUNTF = rng.Count - (cHard + cMand - cBoth)
End If

End Function

To use:

=COUNTF(a, b, B5:B13)

a & b can either be TRUE or FALSE (1 or 0)
B5:B13 contains the questions

To count all hard questions: =COUNTF(1, 0, questions)
To count all mandatory questions: =COUNTF(0, 1, questions)
To count non-mandatory hard questions: =COUNTF(1, 0, questions)-COUNTF(1, 1, questions)

share|improve this answer
Great. Thanks. Will try the code way. Funnily I though about creating a stand alone program, but haven't thought of doing it in Excel. Hope this code will work in 2011. – Avi Jul 6 '11 at 10:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .