4

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..

Robotnik
  • 2,645
Avi
  • 1,058

1 Answers1

3

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
Else
    COUNTF = rng.Count - (cHard + cMand - cBoth)
End If


End Function

To use:

=COUNTF(a, b, B5:B13)

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

example:
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)

Community
  • 1
Ellesa
  • 11,185