Excel Solution Required

Question

Hello hive mind,I'm using Excel on a Mac.&nbsp;PART ONESo I have a cell in Excel that contains a drop down, the drop down has the options: Pass, Merit, Distinction, Fail&nbsp;Later down the spreadsheet I have cells that say:Number of Distinctions and blank cell next to itNumber of Merits and a blank cell next to itNumber of Passes and a blank cell next to itNumber of Fails and a blank cell next to it&nbsp;When someone selects, say a distinction, I'd like the 'blank cell' to show it as a 1, if I get another it changes to 2 etc etc etc...&nbsp;Does anyone know how this can be achieved?&nbsp;PART TWOIn the exact same scenario, my team highlight various cells red or green using the 'fill' tool. Is there anyway to calculate how many reds and how many greens in total I get across a section of the spreadsheet?&nbsp;Thanks to everyone who comments 🙂&nbsp;Brendan

hansvogelaar · Answer

BrendanKingdom&nbsp;
PART ONE:
Let's say the cells with the drop-down are A2:A100.
&nbsp;
Number of cells with Distinction:
=COUNTIF(A2:A100, "Distinction")
And similar for the other options.
&nbsp;
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 · Answer

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 · Answer

BrendanKingdom&nbsp;
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.
&nbsp;
If necessary, you could use another column to record additional needed information.
&nbsp;
Without knowing the setup, it's difficult to provide more detailed help.

brendankingdom · Answer

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 · Answer

BrendanKingdom&nbsp;
Press Option+F11 to activate the Visual Basic Editor.
Select Insert &gt; 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.
&nbsp;
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.
&nbsp;
Save the workbook as a macro-enabled workbook (*.xlsm) and instruct users to allow macros when they open the workbook.

Forum Discussion

Excel Solution Required

Share

Resources