In the table below, supposing you wish to find the total amount for the items that are already delivered and those that are not yet delivered. This is the formula I used: Function SumByColor (CellColor As Range, SumRange As Range) Application. =SUMIF (C2:C9,C6,E2:E9) You could also hard-code the number in like this: Select All. Select "New" and then enter "CellColor" as the "Name". Based on your example, you have a Low number in cell D2, a High number in cell D3, the range in B1:B12, and cells shaded yellow (color index #6) that should be omitted from the average calculation. xml ¢ ( ÌVËnÛ0 ¼ è? ¼ ´ ŠÂv izê#@Ò X“k‰5E $ãØ ß¥¬$n H5, ¾èAigfw%îL/7µÎÖ胲fÆΊ ËÐ +•)gì×Ý×ü. Select User Defined as the category, and then select CountCellsByColor as the function to use. Press Enter. Finally, press enter, and specific cells will be summed up based on color coding. Ive tried all kinds of formulas and Im not sure if I need to select all cells in column G then go to Conditional. Sum based on blank or non-blank cells. Re: Sumif cell is certain color. VBA Code: Function ColorOf(aRange As Range) As. Countif Color. Glimmer_III • 1 yr. If the fonts are colored manually, then you will require VBA, as Excel formulas. Re: sum if cell is certain color From a database perspective, just assign 0/1 or whatever to a separate column. Take a look at the motion picture: 3. 📌 Steps: Initially, select the output cell D12. For example, the simplest SUMIF formula =SUMIF (A2:A10,">10") adds the values in cells A2 to A10 that are larger than 10. =IF (COUNTIF (B2:B4,"green")>=2,"pass","fail") This works when the cells have the text "Green" in them. If it isn't, performance may. All values are in column G) With Colors:Article Link: this video, I will show you how to count cells based on color in. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John. 3. But if the sum on the cell is only partially covered that cell needs to be colored only partially. Use Excel SUMIF Function with Cell Color Name. Font. See Calculate values using data in table cells in Numbers on Mac. Now you have identified the cell with yellow background by advanced Find and Replace. At this. Apply a formula to each cell in column before computing a sum of the cells. Set up two cells with Completed, Incomplete in F1 and F2. To do so, click anywhere inside the data. – Now we’ll use the subtotal function to sum the colored cells as a result of application of the color filter. Initially, select cell E5. To "Sum if condition is true" I've used built-in function SUMIF and for "Sum if background color is not" I've used a function from Script Library. Get the SUM of the colored cells. 000 8. Dim TCell As Range. 1. Want to learn how to sum if cells contain specific text in another cell, you can use the SUMIF function with a wildcard and concatenation. Result. Often you may want to sum values in Excel based on their color. Using @Ritz code I'm trying to modify his code and I have. In cell E2 and E3, give the cells the color for which you want to get the sum. I have come up with something but it has it's quirks, if your "Quoted Amount" is in cell "A1" then enter "=ColourSum(A2:D2)" in "E2", the quirky part is if you change the cell colour normally it will not update without you clicking in the formula in the formula bar, however if you change the colour using the format painter it will change automatically (if. Step 2In this article, you will learn new effective approaches to summing and counting cells in Excel by colored. The following custom function totalColor (cells,color) accepts a cell or a range of cells you want to check and a color of your choice. To define criteria, SUMIFS supports various logical operators (>,<,<>,=) and wildcards (*,?,~). Now, we add a small macro. Steps: First, go to the Formulas and select Define Name. CELL a macro function. Add this Function to your VBA module in order to return a cells interior color index: Function ColorIndex(rng As Range, _ Optional text As Boolean = False) As Variant Dim cell As Range, row As Range Dim i As Long, j As Long Dim iWhite As Long, iBlack As Long Dim aryColours As Variant If rng. Select the data range and click Kutools Plus > Count by Color. I am not very good at formulas in excel. On the other hand, if you need to find the sum of sales. Note that I used "ColorIndex" instead of "Color" to trigger the count increment. Step 1 – In cell C2, which is the first cell where you want the running total, enter. Sum(icell) + SumColor End If Next icell End FunctionThis column will store the values that we use to perform the SUMIF or COUNTIF or IF. GET. 1. I'd use these entries to set up the conditional formatting to give you the colours you want. The second, third and fourth parameters are RGB code of the color. I tried the advice on the following links: superuser. Step 2: Create formulas to count and sum cells by background color. Volatile sum_color = 0 cell_color = ref_color. On the Data tab, click Filter in the Sort. Insert the formula =SUMIF in a separate blank cell. And then, click OK button,. " This places a filter button (arrow) next to each column header. 2. Offset (0, 1). Click "Sort & Filter" and choose "Filter. tshives26. Ctrl A will enable you select all matching results. To use this function, simply select the range of cells that you want to count and then enter the following formula into a cell: =SUMPRODUCT (-- (A1:A10="pink"))Sadly, there is no event to capture a cell color change unlike capturing a cell value change. The Syntax of SUMIF Function is as under: =SUMIF(range, criteria [, sum_range]) Here, ‘ range ’ refers to the cells that you want to be evaluated by the ‘ criteria ’. Format only cells that contain section, and if you want to change font color if cell value is negative, you can choose from the middle list, and then type into the right text box. Jump down to the "Refers to" part and enter the. Use sumif function, for e. To turn on filters, simply select any cell within the data range and then the following Ribbon icon: Data > Filter. Then, pick the Filter option. Learn how to SUM or COUNT cells based on their background fill color. This will apply a filter to all the headers. In other words, I only want to see the sum of red cells and exclude green cells. Figure 1. Each condition is provided with a separate range and criteria. On the Home tab, in the Styles group, click Conditional formatting > New Rule…; In the New Formatting Rule window, select Use a formula to determine which cells to format. I was looking into conditional formatting, but didn't seem to find anything that would work. – G-Man Says 'Reinstate Monica'Click a cell to select it, or enter its table address (for example, F1). 07-16-2014, 04:52 PM #14. are so much simpler. If you've chosen to work with only one color, click the Color Picker icon next to the Pick any cell with color field and select a pattern cell in your table that represents the formatting you need. Function SumIfColour(SumRange As Range, CriteriaRange As Range, Criteria As Variant, _ ColourRange As Range, ColourCell As Range) As Double Dim i As Long Dim r As Double For i = 1 To SumRange. Read this tutorial to learn how to use the SUMIF function to add the contents of cells based on their color. Interior. Count If. Open your data set and fill the cells with necessary colors. In the Function Arguments dialog box, specify the data range and color index cell that you want to sum by font color, see screenshot:. The second argument, criteria, is the criteria to apply, along with any logical operators. thank you in. Any help would be appreciated. Font. You can use the CELL ("color", REF) function IF AND ONLY IF the formatting on the cells is set so that the Green is positive and the Red is negative (or vice versa). by Svetlana Cheusheva, updated on March 22, 2023 In this article, you will learn new effective approaches to summing and counting cells in Excel by color. Generic Formula =SUMIF(color_range,criteria,number_range) According to Microsoft Excel SUMIF is defined as a function that “Adds the cells specified by a given condition or criteria”. Im having a lot of trouble figuring this out and after a ton of search-fu I need to ask an expert. To determine Sum based conditional formatting rule with formula =B2=MAX (Relative Range): Option Explicit Public Function ColorFunction (rRange As Range, Optional getSum As Boolean = False) Dim cel As Range, r As Long, c As String, ur As Range, lr As Long, v As Long For Each cel In rRange With cel If Len. sum_color = 0 Defining the initial value for the variable. Enter the range of cells that you want to count in the Select range input box. if so then you could use a SUMIF () using those codes in the range F9:F160. The counts will update. to get sum of cells values between colored cells you can use something like this: Function CountCcolor(range_data As Range, criteria As Range) Dim datax As Range, CountVal, x% For Each datax In range_data If datax. The CELL function returns information about cells, for instance the format that numbers or dates are displayed in and the column width. I have cells in a column, some colored yellow, some not. the formula can be located in another sheet. }, sum_range)) Please remember that this formula works based on OR logic - a cell is. Count If CriteriaRange(i). What I would like is a formula that I can enter in all the cells of the column that would say: "If this particular cell is coloured yellow then it should equal O43, otherwise it's to be left blank. For example, the formula “=SUMIF (B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. In this example, let’s say we want to sum all the red-colored cells in column A. Then, drag the fill handle downwards to get other results. (red text or something)4. The Format Cells function in Excel can help you to format the values as specific color based on the positive or negative numbers, please do as this:. Select All. g. colored-cell-criterion is a colored cell that defines the background color to filter and sum the data. The syntax of our function and its arguments are as follows: =COUNTIF (range, criterion) range — a range of cells where we want to count a certain value. Highlighting cells brings attention and serves as a color code. range - the range of cells to be evaluated by your criteria, required. Next, select the “Formulas” tab and select “Sum by Color” from the ribbon. Nevertheless, with some tricks and indirect methods, we can still achieve this. Data entered into A1=5 Data entered into A2=6 Formula entered into A3=A1-A2 (value would be -1) The condition I need is if A3 is negative, the cell or text in A1 will be reformatted to throw up a flag. It cannot detect or compare the format attributes of the cell or. Value > 0 Then iNoColorCount = iNoColorCount + 1sum_range Optional. Step 3 – Use SUBTOTAL function to sum the colored cells only. Excel SUMIF Function Based on Cell Color: 4 Methods 1. " You'll see the colors you're using in a pop-out menu, so click the color you want. The code is in Module 1 (right click on Tab name & navigate to module 1) Please Login or Register to view this content. Cells If OfText = True Then 'sums by text color OK = (Rng. #5. Hi,I don't know if this is possible or not. Value Else End If Next cell SumBackGroundColor = i End Function. =SUM (original column)-SUMIF (new column,”red”,original column) Or you could just copy and paste values from the red sum, then get the total, and subtract that from the total. Enter the range containing colored cells you want to sum and select color of the cells. . If this doesn't work see htt. Regarding the range of the sumif: I know that R[-1] references the cell directly above each grey cell but how am I supposed to reference the cell directly after the above grey cell?This video will demonstrate how to count and sum based on cell background colour using the GET. finds the average/min/max values among those highlighted cells. You can additionally try to sum the values based on color too. Font. ICol = CellColor. This is in effect “sum by color” – so if you know Excel’s 56 color palatte and you know for example that color 4 is light green then the following call: Color_By_Numbers (“A1:P20”,4) will sum the values for all the cells in the range A1:P20 that are light green in color. Put an x in column B next to each value you don't want to sum. For example, the number 3 represents the color red. As we can see in the above screenshot, the sum of yellow-color cells is coming at 190, which the summed value is obtained in example-1 and example-2. 2 Answers. Bold = True Then SumIfBold = SumIfBold + cell End If Next cell End Function. place this in a standard VBA. ; Set up pattern colors Pick a cell from the sheet to use its colors for calculations or choose the hues from special palettes. The steps to apply the SUBTOTAL function to perform Sum By Color are as follows: Suppose we would like to highlight those cells that are negative in values for indication purposes that can be achieved. The Find and Replace window appears. Using SUMIF Function to Sum If Cell Color Is Red in Excel In our first method, we’ll use the SUMIF function to get our job done. Another useful link is here which indicates even these user defined functions might have problems if the cell is. CELL and count them to find out the number of cells highlighted in the same color. The function used is. Mike H. This will launch the Name Manager dialog box. Press on the Options>> button. Sum together all numbers in Column I that have a green colour inOption Explicit Function SumColor (MatchColor As Range, sumRange As Range) As Double Dim cell As Range Dim myColor As Long myColor = MatchColor. Font. Use a different method to 'flag' which cells you want to not include in the sum. It sums and counts colored cells in Google Sheets by their font, fill, or both colors using 13 aggregation functions. After pasting the above code, close the module window, then apply the following formulas: Count cells based on the specific background color: Copy or type the formula provided below into your desired cell for the result. 2 Count of Colored Cells. Counting cells based on color. Type “=SUMIFS (” into the formula bar. Arguments can be a hardcoded constant, a cell reference, or a range. Excel sum based on Automatic font color. For this, use one of the following criteria in your Google Sheets SUMIF formulas: Sum if blank: "=" to sum cells that are completely blank. 2. In the New Name dialog box, enter “Color” for Name and the formula =GET. Pros. ColorIndex = 1 Then Blacksum = Blacksum +. Windows. It's not possible without VBA. ' Page 84 Function SumByColor(CellColor As Range, SumRange As Range) Dim myCell As Range Dim iCol As Integer Dim myTotal iCol = CellColor. Then you can use sumifS for the additional criteria. Value End If Next rngCell ColorSum = total End Function. To sum the cell values with a specific cell color, please apply below script code. Public Function SumBackGroundColor(Selection As Range) As Long Dim i As Long Dim cell As Range For Each cell In Selection If cell. In my example, it looks at cells in column B, C & D respectively. Click New Step 4. Click Tools > Script editor to go the project window, and click File > New > Script file to insert another new code module, then, in the prompt box, please type a name for this script, see. Step 2 – In cell C3, enter the below formula: =C2+B3. . There are 13 functions in total to calculate your colored cells. Example table:Click OK. CELL (38,Sheet1!G4) Where Sheet 1!G4 is the cell that represents the background color. Select the blank cell you place the counting results, and click Kutools > Kutools Functions > Statistical & Math > COUNTBYCELLCOLOR. CELL Function to Sum If Cell Color Is Red in Excel. The UDF in cell D2 sees the color of the cell it is in, and sums cells having that color. Of course, this would be easy to do using standard VBA functions/properties like. How to Sum Colored Cells in Excel. ColorIndex 'get the target color For Each myCell In SumRange 'look at each cell in the designated range If myCell. Required. The yellow highlighted values are the cells which are matching with the conditions. The second argument is a hexadecimal color value quoted as a string To view or set the hex value of a cell open the color picker ref1. Change the =0 to =1 to sum every second row, but starting at the first row. Color = ReferenceColor Then Result = Result + 1 Next Cell End If. This loop is counting the number of cells that match our ReferenceColour. 📌 Example 2: Find Discounted Price Based on Cell Color Using IF Statement. The second argument, criteria, is the criteria to apply, along with any logical operators. CELL, you need to extract the color codes with GET. To get. Overview. How To Sum Cells based on Background Color in Excel || Excel TipsIn this tutorial, let us learn How to Sum Cells based on Background Color in Excel. Sum if - Horizontal Sum: Count / Sum If - Cell Color: INDIRECT Sum: Sum If - Across Multiple Sheets: Sum If - By Month: Sum If - Cells Not Equal To: Sum If - Not Blank: Sum if - Between Values:. or handle colored cells in each individual row: This is very helpful since you won't have to copy the formula to sum or count colored cells in each column/row respectively. Here’s how:Get to know Function by Color add-on. Then as usual we will apply the SUMIF function to calculate the summation based on a certain color. Go to Data –> Sort and Filter –> Filter. Create a Table object The data set now has several colors, but as is, they’re not much help for. Cells. Select the range of cells that you want to sum. Apply VBA Codes to Sum Green Cells Only. Then, drag the fill handle downwards to get other results. 3. In this method, we will use VBA code to find the sum if the cell color is green in Excel. We will click OK twice again, to finish this step. This is how I got it to work: Open Extensions > Apps Script. The syntax of this function is very straightforward and requires just one argument:We would like to show you a description here but the site won’t allow us. sum_range Optional. The SUMIFS function is a widely used function in Excel and can be used to sum cells based on dates,. Print. Mike H. simplifiedexcel. This is how we can filter the red cells. Click the pop-up menu and choose a text style, such as bold or italic, or a cell fill, such as red or green. CELL function is a hidden function in Excel. Using @Ritz code I'm trying to modify his code and I have this. CELL function along with the SUMIF function to sum up the colored cells in Excel. . 3. Select the SUM function from the Use function list. Format the cell value red if negative and green if positive with Format Cells function. Next, select the “Formulas” tab and select “Sum by Color” from the ribbon. For the cells that are matching these conditions, we will fill them with blue color. Thanks! :-?When a customer pays, he highlights that cell in yellow by filling the cell with a yellow background color. Open your data set and fill the cells with necessary colors. For SUM of Conditional Color: Function SumConditionColorCells(CellsRange As Range, ColorRng As Range) Dim Bambo As Boolean Dim dbw As String Dim. The criteria can include dates, numbers, and text. 4. Try: Sub sumallrowcolored () Dim i As Integer Dim e As Integer e = 1 For i = 2 To 168 Step 2 e = i - e For Each cell In Range ("i:i") If cell. Color For Each cell In sumRange If cell. 00:00 Intro00:25 SUM by Color Using SUBTOTAL Function02:05 SUM by Color U. Next loop to check each cell in the InputRange. You may need to allow permissions. 3. Interior. By luizmachado in forum Excel Programming / VBA / Macros Replies: 7 Last Post: 03-06-2016, 10:51 AM. How to Use SUMIF to Sum Cells Based on Background Color. Get Color Code. The picture shows where three User Defined Functions (UDFs) have been entered into cells D2, F2, and H2 to demonstrate how you can sum or count colored cells. : SUMIF (B1:B6,"Yellow",A1:A6) Register To Reply. So say your numbers to sum are in A. Depending on your. e. " CF type list). CELL (38,Sheet1!C3) in. Color. We have a. Click the Color Picker icon and select a cell that represents the background and/or font color you want to sum and count by. Type “,” and then select the first criteria range. Go to Data –> Sort and Filter –> Filter. First, select any cell from the dataset. Sum : If you put TRUE, the the colored cells will be sumed up, otherwise it will count the colored cells. Here are the steps count colored cells in Excel: In any cell below the data set, use the following formula: =SUBTOTAL (102,E1:E20) Select the headers. 2 Count of Colored Cells. ; Criteria (required) - the condition that must be. g. Now you need to sum the value of the highlighted cells that meet the conditional formatting criteria. I am trying to use the SUMIF function to sum only the cells in the range that are colored. Figure A A conditional scale set applies different colors to the invoice values in column B. where color is the named range D5:D16. VBA Code:. Colour all your data red with normal formatting. This will return the “color classification” for that color; For example, as shown below, the blue colored cells have a color classification of “00B0F0” Drag this formula down to apply to all your colored cells; 3. Color =. Open a Microsoft Excel file where you’ve applied cell background colors (“fill color”) and column headers. The color key is a cell outside the table with same cell color as the other highlighted cells. Output: Sum of students with scores greater than 75. Select the range, click on the Sum by Color icon and choose one of the options: Sum and Count by one Color or Sum and Count by All Colors. In this example, the goal is to sum Total when the corresponding Color is either "Red" or "Blue". SUMIF Cells with Certain Colors. If you want to know the sum of the number of Green colored cells or count the Green colored cells, you can use the Filter option and the SUBTOTAL function. The add-on in question that can help sum up colored cells is called Function by. e. . We can use the ColorIndexOfRange function to get the sum of the values in those cells whose color index is some specified value. The way these color code functions are set up, the color code column has to be on the right of the colored cells. Sum the Transaction Total if the price per unit is greater than 20. Actually, it is what does the trick and applies formatting to the whole row based on a value in a given cell. Select cellsD3:D8 Step 2. The SUMIFS function sums the cells in a range that meet multiple conditions, referred to as criteria. Highlight the range you wish to count. Highlight the range that contains all the colored cells. So say your numbers to sum are in A. =COUNTIF (B2:B5;F1) Use the same styles you used in the conditional formatting to format the sums to get the corresponding colours. Interior. Open your data set and fill the cells with necessary colors. CELL function. You could use a VBA function to sum all cells that are colored: Code: Public Function ColorSum (myRange As Range) As Variant Dim rngCell As Range Dim total As Variant For Each rngCell In myRange. On the Excel Ribbon's Home tab, click the Condtional Formatting command. Scope: Workbook. If the currently examined cell’s interior color is red ( 255 ), then add the value to the variable named SumColor and store it in the variable named SumColor. These solutions work for cells colored manually and with conditional formatting in all versions of Excel 2010 through Excel 365. Need to learn how to sum up if cell color is red in Excel? Excel doesn’t have any built-in function, to sum up, if the cell color is red. If a color is changed the event code would need to be called by selecting another cell in the Amounts column. Click OK. But only a little. There are 13 functions in total to calculate your colored cells. In this video we'll look at how to use the SUMIFs function to sum cells that meet multiple criteria. The formulas in cells H5 and H6 use SUMIF to sum amounts. Font. Here is the list of all available colors: redberry, red, orange, yellow, green, cyan, cornflowerblue, blue, purple, magenta, grey, white, black. In the formula text box, enter: =A2<A3. The first argument, range, is the range of cells to apply criteria to. If then Google Sheets. Use with =countColoredCells (A1:Z5,C5) where C5 is the cell with the color to be count. Color. There are a few ways you can approach this problem, each of which I'll address in the following sections. =SUMIF (B1:B100,"<>x",A1:A100) 0. Dim ICol As Integer. To begin with, select the cell or cell range to Highlight. Function SumByColor(CellColor As Range, rRange As Range) Dim cSum As Double Dim ColIndex As Integer ColIndex = CellColor. We can confirm this by manually calculating the sum of each cell with a light green background. Select Insert > Module. Font. =SUMIF (C2:C7,">25000") This formula adds the numbers in the cell range C2 through C7 only if they are greater than 25,000. The Syntax is. Select the color you used to format the cells and click “OK”. Choose the Function by color add-on and Click Install. Hi aman am having the same issue i want to sum cells which are coloured using conditional formatting, the rule i am using in conditional formatting is if values in a2:a20 is less than A1 then make it red color cell. Value End If Next cell. If font color is automatic, returns 0. We can filter by font or fill color using the built-in filter feature of Excel. [SOLVED] SUMIF based on cell font color. On the Create Table dialog box, click the OK button. Save and quite. If I invoke the following macro while A10 is selected then A1,A4 and A7 are colored green: Sub ColorSummands () Dim R As Range Set R = Selection R. Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double Application. Font. In the resulting dialog, check the My Table Has Headers option and click OK. We can use the SUMIF function to perform subtotal by color in Excel. =SUM(B11:B17*(COLORINDEXOFRANGE(B11:B17,FALSE,1)=3)) There is a built-in function in Excel, the GET. For instance, for the example below, I need the first sum to return 6, and then the second sum to return 4. 000 0 4. Replace A1:A10 and B1:B10 with the actual range of. Interior. Interior. Click a cell where you want to put the count result, and then click Kutools > Kutools Functions > Statistical & Math > SUMBYFONTCOLOR,see screenshot: 2. 1. For example, suppose we have the following dataset and we’d like to sum the values in the cells based on the cell colors: The easiest way. Allow me to demonstrate the. Sum(cl, cSum) End If Next cl SumByColor = cSum End Function. ; sum_range - the cells to sum if the condition is met, optional. Enter the SUBTOTAL Function. e. Et voilà!Below is an example on how you can use this function. Ctrl A is the shortcut for “Select All”. Function COUNTConditionColorCells (CellsRange As Range, ColorRng As Range) Dim Bambo As Boolean Dim dbw As String Dim CFCELL As Range Dim CF1 As Single Dim CF2 As Double Dim CF3 As Long Bambo = False For CF1 = 1 To CellsRange. Interior. ben_says January 18, 2013, 4:09pm #1. Before using the formula you have to identify RGB of conditional formatting color, then u can color manually by entering RGB in blank cell and then function will work. Hit [Choose Format From Cell] and we select green here. The Cell("color", E10) you are using only returns 2 values, 1 or 0. It helps to add cells based on numbers, dates, and text matching certain criteria. In the Greater Than tab, select the value above which the cells within the range will change color. I also tried to use the CELL function's color feature, but I couldn't get it. Clear search On the Excel Ribbon, go to "Formulas" and click on "Name Manager". @Omar: I have already edited the answer to explain about the braces and the Ctrl+Shift+Enter. Interior. Additional thing is that I need to sum different columns ex. Enter the following formula, replacing the cell references and criteria with your own. =SUMIFCOLOR ("A1:A5", "ff0000") The first argument is the range quoted as a string. . I want the cell should change color as green if resolved and red as overdue and open as brown and hold as yellow. The first argument is the range of cells containing the colored cells (with numbers in them) that you want to search and the second argument is a cell with the interior and font colors that. In the example above, there are two color classifications. Click Use a formula to determine which cells to format in the Select a Rule Type list box; Enter this formula = highlightredfont(B2) into the Format values where this formula is true text box; Then, click Format button. For instance, if the format in cell A17 is yellow, then the value in cell B17 should be included in the sum. Syntax. Value Then If Len(cell. This helps you sum up values on specific conditions. Value = Criteria And ColourRange(i). 1 COUNTIF Formula with Cell Color. criterion or searching criterion — a value to find and count across the data range indicated in the first argument.