Forum Discussion

BrendanKingdom's avatar
BrendanKingdom
Copper Contributor
Aug 21, 2023

Excel Solution Required

Hello hive mind, I'm using Excel on a Mac.   PART ONE So I have a cell in Excel that contains a drop down, the drop down has the options: Pass, Merit, Distinction, Fail   Later down the spreads...
BI & Data Analysis
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Aug 21, 2023

BrendanKingdom 

PART ONE:

Let's say the cells with the drop-down are A2:A100.

 

Number of cells with Distinction:

=COUNTIF(A2:A100, "Distinction")

And similar for the other options.

 

PART TWO:

If the cells are colored directly (not through conditional formatting), counting cells with a specific color requires VBA, so all users would have to allow macros. And it would only work in the desktop version of Excel for Windows and Mac, not in Excel Online, nor on iOS/Android.

Do you really want this?

BrendanKingdom's avatar
BrendanKingdom
Copper Contributor
Aug 21, 2023
Thanks Hans, hmmm... with regard to Part Two, I don't know what VBA is, but basically in the cell there is a deadline date, my team colour it red if it's not been met and green if it has, I simply wanted to output the number of green cells and the number of reds, is there another way to do this?

Thank you in advance
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 21, 2023

    BrendanKingdom 

    If the cell is colored red when the deadline date has been passed, you could use a conditional formatting rule to so.

    And you could use a COUNTIF formula that looks at the dates to count those that have passed.

     

    If necessary, you could use another column to record additional needed information.

     

    Without knowing the setup, it's difficult to provide more detailed help.

    • BrendanKingdom's avatar
      BrendanKingdom
      Copper Contributor
      Aug 21, 2023
      Hi Hans, thank you for your help.. so, the dates are fixed, the team doesn't need to enter the dates at all, the team simply colours the cell red or green. Based on that - how would I get the data I require?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 21, 2023

        BrendanKingdom 

        Press Option+F11 to activate the Visual Basic Editor.

        Select Insert > Module.

        Copy the following code into the module:

        Function CountColor(MyRange As Range, ColorCell As Range) As Long
    Dim MyColor As Long
    Dim MyCell As Range
    Application.Volatile
    MyColor = ColorCell.Interior.Color
    For Each MyCell In MyRange
        If MyCell.Interior.Color = MyColor Then
            CountColor = CountColor + 1
        End If
    Next MyCell
End Function

        Switch back to Excel.

        Use as in the example below:

        The formula in D1 is =CountColor($A$1:$A$14,C1)

        A1:A14 is the range in which we want to count; C1 is a cell that contains the color we want to count.

         

        Warning: when the user applies or changes a color, the formula result will not be updated automatically.

        This will happen the next time Excel calculates any formula. You can force it by pressing F9.

         

        Save the workbook as a macro-enabled workbook (*.xlsm) and instruct users to allow macros when they open the workbook.

Resources