excel formula help

[Maddy]

Hi Team,

Can someone please help me to find out formula to get the total count of cells in red.

I have attached the sheet for reference.

 

[Ramesh]

I have done this using a user defined function in excel

Dim cell As Range
Dim colorcount As Integer
Function retColorcodeCount(rng As Range, colorcell As Range)
    colorcount = 0
    For Each cell In rng.Cells
        If cell.Interior.Color = colorcell.Interior.Color Then
            colorcount = colorcount + 1
        End If
    Next
    retColorcodeCount = colorcount
End Function

In cell AG2, apply the color as cell background which you would like to count and cell AF2 call the function with the cells where the color's are highlighted and cell reference that helps us with the color that we would like to count...

 

[Maddy]

is it possible without VBA Ramesh?

 

[bosco_yip]

is it possible without VBA amesh?

Count Cells in background color red without VBA



1. Select AF2 >> Define Name >>

>> Name : CountColor

>> Refer to :

=SUMPRODUCT(0+(GET.CELL(63,IF(1,+OFFSET($A2:$AE2,,COLUMN($A2:AE2)-MIN(COLUMN($A2:$AE2)))))=3))

2] In AF2, formula copied down :

=CountColor

p.s. : Get.Cell() is a Excel 4 Macro function, so you need to save file as xlsm.

Regards
Bosco

 

[Deepak Sharma]

Excel Formula's do not see color as an attribute. There is currently no way for the formulas to count colored cells without using VBA or unless there is some logic behind the coloring that Excel can use as criteria.

Regards,

 

[Ramesh]

Agree with Deepak. There is no formula driven way of counting the cells based on the interior color.

You might be using some criteria, using which you are highlighting those cells in Red color, use the same criteria in COUNTIF or COUNTIFS functions to count the cells

 

[bosco_yip]

Count Cells in background color red without VBA

View attachment 52359

1. Select AF2 >> Define Name >>

>> Name : CountColor

>> Refer to :

=SUMPRODUCT(0+(GET.CELL(63,IF(1,+OFFSET($A2:$AE2,,COLUMN($A2:AE2)-MIN(COLUMN($A2:$AE2)))))=3))

2] In AF2, formula copied down :

=CountColor

p.s. : Get.Cell() is a Excel 4 Macro function, so you need to save file as xlsm.

Regards
Bosco

A bit revised with adding a color cell criteria in AF1 (red color)



1] The defined name formula revised to

=SUMPRODUCT(0+(GET.CELL(63,IF(1,+OFFSET($A2:$AE2,,COLUMN($A2:AE2)-MIN(COLUMN($A2:$AE2)))))=GET.CELL(63,$AF$1)))

2] See attachment

Regards
Bosco