Excel COUNTIF with conditional formatting

0
votes

I have one sheet (Attendance Sheet) where I have entered attendance of employees manually.

Now there are a few conditional formattings applied on the sheet. For example:

  1. If someone enters the office after 9:30AM a yellow color will highlight the cell.
  2. If someone is absent a "A" in the cell will highlight the cell as red color.
  3. If someone is on leave a "Leave" in the cell will highlight the cell as green color. and so on...

This all works fine and flawlessly.

Now to the issue.

I have manually set the colors in the "Legend" area in the sheet for reference for the formula, countif.

https://imgur.com/a/FLsAV

When I want to use COUNTIF to calculate the total number of days the employee was late, absent or on leave, I select a cell, enter the formula, for example I want to count the number of days the employee was Late, I use, =COUNTIF(H1:H30,A4)... (Where A4 is the cell of the Yellow color and H1:H30 is the range).

and the result returns "0" while I have Yellow cells highlighted via conditional formatting. I have tried working on this several hours via google and YouTube but to no avail.

Please help.

2
excelcolorsformattingconditionalcountif
Is the yellow-coloured cell A4 empty? Or does it contain the word "Late"? In either case, your countif formula is going to count the cells that contain the same value as A4, not the same colour. – MattClarke
Hello Matt, Yes the cell is empty. The problem persist if there is text or even if there isn't any. Any solution to make this work? – Fahad Ahmed

2 Answers

0
votes

Use countifs() function to calculate all the criteria at the same time. And also make sure that cells with colors are not empty. There's must be some Relative data in it. you cannot Count values based on only cell color (without VBA).

0
votes

Open Developer Tab(shortcut key - Alt+PF11)

Insert a module and insert below code in it.

 Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
 Dim rCell As Range
 Dim lCol As Long
 Dim vResult
   lCol = rColor.Interior.ColorIndex
   If SUM = True Then
    For Each rCell In rRange
   If rCell.Interior.ColorIndex = lCol Then
    vResult = WorksheetFunction.SUM(rCell, vResult)
   End If
  Next rCell
  Else
  For Each rCell In rRange
  If rCell.Interior.ColorIndex = lCol Then
  vResult = 1 + vResult
  End If
  Next rCell
  End If
  ColorFunction = vResult
End Function

Suppose you have coloured cells(eg: yellow color) in A column and legend(yellow) in D1.Now you can write your formula like this in E1 as =colorfunction(D1,A:A,FALSE)

Suppose you have coloured cells(eg: yellow color) in A column with data and legend(yellow) in D1.Now you can write your formula to get sum of all with yellow color like this in E1 as =colorfunction(D1,A:A,TRUE)

Note: The only problem I see in this if you are not changing data then it will not update automatically.In this case you need to go to that formula cell(eg:E1) and select & press enter key.

Screenshot