Excel Tips: Count/sum cells by color (background, font, conditional formatting)

일상적인 작업에서 색상 표시는 중요한 데이터를 빠르게 구분하고 강조하는 인기 있는 방법입니다. 하지만 특정 색상(채우기 색상, 글꼴 색상, 조건부 서식)에 따라 셀 데이터를 계산하거나 합산하려면 어떻게 해야 할까요? 기본적으로 Excel은 색상별로 계산하거나 합산하는 직접적인 기능을 제공하지 않습니다. 그럼에도 불구하고 몇 가지 트릭과 간접적인 방법을 사용하면 이를 해결할 수 있습니다. 이 문서에서는 색상별로 데이터를 계산하거나 합산하는 방법을 살펴보겠습니다.

배경색에 따라 셀 계산 및 합산하기

• 사용자 정의 함수 사용하기
• 강력한 기능인 Kutools for Excel 사용하기
• 필터와 SUBTOTAL 사용하기

글꼴 색상에 따라 셀 계산 및 합산하기

• 사용자 정의 함수 사용하기
• 간단한 기능인 Kutools for Excel 사용하기

조건부 서식 색상에 따라 셀 계산 및 합산하기

• VBA 코드 사용하기
• 스마트한 기능인 Kutools for Excel 사용하기

비디오: 색상에 따라 셀 계산 및 합산하기

배경색에 따라 셀 계산 및 합산하기

예를 들어, 아래 스크린샷과 같이 다양한 배경색으로 채워진 데이터 범위가 있다고 가정해 보겠습니다. 특정 색상에 따라 셀을 계산하거나 합산하려고 하는 경우, Excel은 배경색에 따라 셀을 계산하거나 합산하는 직접적인 기능을 제공하지 않습니다. 그러나 약간의 창의성과 유용한 기술을 활용하면 이 작업을 수행할 수 있습니다. 이번 섹션에서는 몇 가지 유용한 방법을 살펴보겠습니다.

사용자 정의 함수를 사용하여 배경색에 따라 셀 계산 및 합산하기

여기서는 Excel에서 이 작업을 해결하기 위해 사용자 정의 함수를 생성하고 사용하는 방법을 보여드리겠습니다. 다음 단계를 따르세요:

1단계: VBA 모듈 편집기를 열고 코드 복사하기

1. Alt + F11 키를 눌러 Microsoft Visual Basic for Applications 창을 엽니다.
2. 열린 창에서 Insert > Module을 클릭하여 새 빈 모듈을 생성합니다.
3. 그런 다음 아래 코드를 빈 모듈에 복사하여 붙여넣습니다.
   VBA 코드: 배경색에 따라 셀 계산 및 합산하기

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

2단계: 배경색에 따라 셀을 계산 및 합산하기 위한 공식 생성하기

위의 코드를 붙여넣은 후 모듈 창을 닫고 다음 공식을 적용하세요:

• 특정 배경색에 따라 셀 계산하기:
  아래 제공된 공식을 원하는 결과 셀에 복사하거나 입력하세요. 그런 다음 채우기 핸들을 아래로 드래그하여 다른 결과를 얻으세요. 스크린샷 참조:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  참고: 이 공식에서 G2는 일치시키려는 특정 배경색이 있는 참조 셀입니다; $B$2:$E$12는 G2의 색상과 일치하는 셀의 개수를 계산하려는 범위입니다; FALSE는 일치하는 색상을 가진 셀을 계산하는 데 사용됩니다.
  
• 특정 배경색에 따라 셀 합산하기:
  아래 제공된 공식을 원하는 결과 셀에 복사하거나 입력하세요. 그런 다음 채우기 핸들을 아래로 드래그하여 다른 결과를 얻으세요. 스크린샷 참조:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  참고: 이 공식에서 G2는 일치시키려는 특정 배경색이 있는 참조 셀입니다; $B$2:$E$12는 G2의 색상과 일치하는 셀의 개수를 계산하려는 범위입니다; TRUE는 일치하는 색상을 가진 셀을 합산하는 데 사용됩니다.
  

강력한 기능을 사용하여 배경색에 따라 셀 계산 및 합산하기

프로그래밍에 익숙하지 않은 분들에게 VBA는 상당히 복잡하게 느껴질 수 있습니다. 여기서는 강력한 도구인 Kutools for Excel을 소개하겠습니다. Count by Color 기능을 통해 몇 번의 클릭만으로 배경색에 따라 쉽게 계산(개수, 합계, 평균 등)할 수 있습니다. 인상적으로도 Count by Color 기능은 배경색에 그치지 않고 글꼴 색상과 조건부 서식까지 구분하고 계산할 수 있습니다.

Kutools for Excel을 다운로드하고 설치한 후, 먼저 특정 배경색에 따라 계산하거나 합산하려는 데이터 범위를 선택하세요. 그런 다음 Kutools Plus로 이동하여 Count by Color를 선택하세요.

Count by Color 대화 상자에서 다음 작업을 지정하세요:

1. Color method 드롭다운 목록에서 Standard formatting을 선택하세요;
2. Count type 드롭다운 목록에서 Background를 지정하고, 대화 상자에서 각 배경색에 대한 통계 결과를 미리볼 수 있습니다;
3. 마지막으로 Generate report를 클릭하여 계산된 결과를 새로운 워크북으로 내보냅니다.

결과:

이제 통계가 포함된 새로운 워크북을 받게 됩니다. 스크린샷 참조:

Filter 및 SUBTOTAL 기능을 사용하여 배경색에 따라 셀 계산 및 합산하기

아래 스크린샷과 같은 과일 판매 테이블이 있다고 가정해 보겠습니다. 우리는 Amount 열의 색상이 있는 셀을 계산하거나 합산할 것입니다. Amount 열.

1단계: SUBTOTAL 함수 적용하기

SUBTOTAL 함수를 입력하기 위해 빈 셀을 선택하세요.

• 같은 배경색을 가진 모든 셀을 계산하려면 다음 공식을 입력하세요:

  =SUBTOTAL(102, F2:F16)

• 같은 배경색을 가진 모든 셀을 합산하려면 다음 공식을 입력하세요:

  =SUBTOTAL(109, F2:F16)

• 참고: 위의 공식에서 102는 숨겨진 셀을 제외한 필터링된 목록에서 숫자 값을 계산하는 것을 나타내며; 109는 숨겨진 셀을 제외한 필터링된 목록에서 값을 합산하는 것을 나타냅니다; F2:F16은 계산될 카운트 또는 합계의 범위입니다.
• 

2단계: 특정 색상에 따라 셀 필터링하기

1. 테이블의 헤더를 선택하고 Data > Filter를 클릭하세요. 스크린샷 참조:
   
2. Click the Filter icon  in the header cell of the Amount column, and click Filter by Color and the specified color you will count by successively. See screenshot:
   

Result:

After filtering, the SUBTOTAL formulas automatically count and sum the colored cells in the Amount column. See screenshot:

Count and sum cells based on font color

Want to count or sum cells based on their font color in Excel? Let's say you have the data, like in the given screenshot, with cells containing texts in red, blue, orange and black color. Excel doesn't make this easy by default. But don't worry! In this section, we'll show you some simple tricks to do just that.

Count and sum cells based on font color with User Defined Function

To count and sum cells with specific font colors, the following User Defined Function may help you to solve this task. Please do with the following steps:

Step 1: Open the VBA module editor and copy the code

1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the opened window, click Insert > Module to create a new blank module.
3. Then, copy and paste the below code into the blank module.
   VBA code: Count and sum cells based on font color

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

Step 2: Create formulas to count and sum cells by font color

After pasting the above code, close the module window, then apply the following formulas:

• Count cells based on the specific font color:
  Copy or type the formula provided below into your desired cell for the result. Then, drag the fill handle downwards to get other results. See screenshot:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  Note: In this formula, G2 is the reference cell with the specific font color you want to match; $B$2:$E$12 is the range where you want to count the number of cells of G2's color.
  
• Sum cells based on the specific font color:
  Copy or type the formula provided below into your desired cell for the result. Then, drag the fill handle downwards to get other results. See screenshot:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Note: In this formula, G2 is the reference cell with the specific font color you want to match; $B$2:$E$12 is the range where you want to count the number of cells of G2's color.
  

Count and sum cells based on font color with an easy feature

Looking to effortlessly count or sum cell values in Excel based on font color? Dive into Kutools for Excel's Count by Color feature! With this smart tool, counting and summing cells by specific font color becomes a breeze. Discover how Kutools can transform your Excel experience.

After downloading and installing Kutools for Excel, first, select the data range that you want to count or sum cells based on a specific font color. Then, click Kutools Plus > Count by Color to open the Count by Color dialog box.

In the Count by Color dialog box, please specify the operations:

1. Select Standard formatting from the Color method drop down list;
2. Specify Font from the Count type drop down list, and you can preview the statistical results for each font color in the dialogue box;
3. At last, click Generate report to export the calculated results to a new workbook.

Result:

Now, you have a new workbook displaying the detailed statistics based on font color. See screenshot:

Count and sum cells based on conditional formatting color

In Excel, you may commonly use the Conditional Formatting to apply specific color to cells that meet certain criteria, making data visualization intuitive. But what if you need to count or sum those specially formatted cells? While Excel doesn't offer a direct way for this, here are ways to maneuver around this limitation.

Count and sum conditionally formatted cells with VBA code

Counting and summing conditionally formatted cells in Excel is not straightforward using built-in functions. However, you can accomplish this task using VBA code. Let's go over how you can use VBA for this:

Step 1: Open the VBA module editor and copy the code

1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the opened window, click Insert > Module to create a new blank module.
3. Then, copy and paste the below code into the blank module.
   VBA code: Count and sum cells based on conditional formatting color

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

Step 2: Execute this VBA code

1. After pasting the code, press F5 key to run this code, a prompt box will appear, please select the data range where you want to count and sum cells based on conditional formatting. Then, click OK, See screenshot:
   
2. In another prompt box, select a specific conditional formatting color that you want to count and sum, and click OK button, see screenshot:
   

Result:

Now, the result, which includes both the count and sum of cells with the specified conditional formatting color, will be displayed in the popped-out box. See screenshot:

Count and sum conditionally formatted cells with a smart feature

If you're looking for other quick and easy methods to count and sum conditionally formatted cells, Kutools for Excel is your go-to solution. Its Count by Color feature can solve this task in just a few clicks. Dive in to discover the efficiency and precision Kutools can bring to your workflow.

After downloading and installing Kutools for Excel, first, select the data range that you want to count or sum cells based on a specific conditional formatting color. Then, click Kutools Plus > Count by Color to open the Count by Color dialog box.

In the Count by Color dialog box, please specify the operations:

1. Select Conditional formatting from the Color method drop down list;
2. Specify Background from the Count type drop down list, and you can preview the statistical results for each conditionla formatting color in the dialogue box;
3. At last, click Generate report to export the calculated results to a new workbook.

Result:

Now, you have a new workbook displaying the detailed statistics based on the conditional formatting color. See screenshot:

Related Articles: