counting cells based on colours within another worksheet

[Costtx]

I would like to create an excel formula which counts the number of cells in a column which are a specific color. an example of this:

I would like the cell B2 to count the number of enquiries which appear in a separate worksheet (Region 1) which match the colour of cell A2. I would like the cell B3 to count the number of enquiries which appear in the region 1 worksheet which match the colour of cell A3 and so on. I would also like to apply this for column C for the region 2 worksheet etc.

Is there an excel formula which will count the cells in the separate worksheet which match the colour of the specific cell and count them?

Thanks, much appreciated if anyone can help.

 

[Rijnsent]

Hi Costtx,
you mean something like this? How to count and sum cells by color in Excel
Cheers,
Koen

 

[Costtx]

Hi Costtx,
you mean something like this? How to count and sum cells by color in Excel
Cheers,
Koen

Hi Rijnsent,


Awesome, that covers half of the equation. It counts the cells based on the color, so that helps, thank you.

What I need now is something which will ensure it only counts it if it appears in a particular Worksheet. For example; there are enquiries coming through from different regions and they are color-coded based on the type of enquiry. So I want the enquiries to be counted in a particular cell only if they are a particular enquiry (color) and if they come from a particular region (worksheet). Does that make sense?

 

[Costtx]

Hi Rijnsent,


Awesome, that covers half of the equation. It counts the cells based on the color, so that helps, thank you.

What I need now is something which will ensure it only counts it if it appears in a particular Worksheet. For example; there are enquiries coming through from different regions and they are color-coded based on the type of enquiry. So I want the enquiries to be counted in a particular cell only if they are a particular enquiry (color) and if they come from a particular region (worksheet). Does that make sense?

I am thinking that if I could somehow combine it with the =COUNTIFS function...

 

[Anthony47]

If the question is still open then my proposal is the following Function:

Function CbyCol(ByRef tCol As Range, ByRef tRan As Range) As Variant
'see https://www.mrexcel.com/board/threads/counting-cells-based-on-colours-within-another-worksheet.1130999/
Dim cArr() As Long, oArr() As Long, I As Long, myC As Range, myMatch
Dim myCC As Long, ckRan As Range
'
Application.Volatile
'Redim Color and Output Arrays:
ReDim cArr(1 To tCol.Rows.Count)
ReDim oArr(1 To Application.Caller.Rows.Count)
'Get target colors:
For I = 1 To tCol.Rows.Count
    cArr(I) = tCol.Cells(I, 1).Interior.Color
Next I
'Set Area to be checked (used):
Set ckRan = Application.Intersect(tRan, tRan.Parent.UsedRange)
'Scan checked area:
For Each myC In ckRan 'tRan
    myCC = myC.Interior.Color
    myMatch = Application.Match(myCC, cArr, False)
'    Debug.Print myC.Address, myMatch
    If Not IsError(myMatch) Then
        oArr(myMatch) = oArr(myMatch) + 1
    End If
Next myC
'Return results:
CbyCol = Application.WorksheetFunction.Transpose(oArr)
End Function

Copy the code in a standard module of your Vba Project, then you can use it in a formula with the following syntax:

=CbyCol(RangeOfColors;RangeToBeChecked)

RangeToBeChecked need to unclude SheetName and Cells Address; for example Sheet2!A1:Z100

If the RangeOfColors refers to multiple lines then the formula MUST be introduced in the form af array formula in a number of cells that matches the number of color; this is necessary even with the newest excel versions that handle Dynamic Array

For example:


The formula in M3 is

=CbyCol(L2:L4,Foglio3!C:C)

and it is entered with Contr-Shift-Enter in cells M2:M4

Since the name of the sheet is embedded into the parameter RangeToBeChecked, the header in M1 is only a reminder; changing the M1 will not modify the RangeToBeChecked.
However you can make the formula depending on the header by using Indirect addressing; for example:

=CbyCol(L2:L4,INDIRECT(N1&"!A:E"))

In this way, changing the header will modify also the RangeToBeChecked

As shown in the example, RangeToBeChecked can include multiple columns
The function is defined as "volatile", thus it will recalculate whenever the "precedents" of the formula changes (but Colors do not imply a "change") or whenever a Calculation runs in the workbook.

Bye