Statistics on the sum and quantity of irregular data

I recently went to an enterprise training excel. Some students asked the following question: What should I do if I want to calculate some irregular cell values in a data area?

The solution is as follows:

1. First, set the font color of cells to be counted to a color that is not used throughout the area;

2. Call the following functions for statistics;

3. parameter description: Rng: the entire area to be counted; Rng2: the cell with the same font color to be counted;

4. Small functions compiled using vba are as follows:

Function SumByColor (Rng As Range, Rng2 As Range) As Double

Dim R As Range

Dim Total As Double

For Each R In Rng. Cells

If R. Font. Color = Rng2.Font. Color Then

Total = Total + R. Value

End If

Next R

SumByColor = Total

End Function

Function CountByColor (Rng As Range, Rng2 As Range) As Long

Dim R As Range

Dim Count As Long

For Each R In Rng. Cells

If R. Font. Color = Rng2.Font. Color Then

Count = Count + 1

End If

Next R

CountByColor = Count

End Function

5. Open the excel VB coding environment;

6. Enter the vbprogramming environment and insert the module;

7. copy the above program to the following position:

 

8. Check the effect according to the following instance verification!