#Excel formula if date range how to
Here are examples to demonstrate how to use this function in different scenarios.Įvaluating the data and the result, the COUNTIF function in Excel returned 1 which means, in our data, there is only 1 unique item, and it is the Watermelon. Large datasets might be tough to handle, oftentimes you would need to know how many unique items there are. =COUNTIF(range, TODAY()) – counts dates that are equal to the current dateīelow are more ways to use this function and TODAY functions together. Here are other sample formulas to help you better understand how to use this function with dates.Īside from this, you can also combine specific Excel Date and Time functions with COUNTIF such as TODAY() to count cells based on the current date. =COUNTIF(range,“>”&B1) – where B1 contains the value January 1, 2019 Criteria “”) – count the dates greater than January 1, 2019 This number can be entered directly into the formula or referred to on the worksheet. This number represents the boundary below which you want to count. This LESS THAN function can be used to count the number of cells that contain values LESS THAN a specified number. If you need to add more, insert another + symbol and repeat for the new range, and so on. 9, 8, 10, 7.1, 9, 8, 77, 11, and 7.11 that gives us the 9 results, which matches below.Īlternatively, if you need to use the COUNTIF formula over a split range, you can work it as per below by simply ADDING two COUNTIFs together.For the example given below its counting these (top to bottom, column B then C in that order): Note that 7 will NOT be counted into the total or anything over 7 will be and that includes fractions, so 7.1 (cell C3) will be included. The result now calculates a total of 9 times where the data is GREATER THAN 7. This, (where E2 has the value 4) is the same as this =COUNTIF(B3:B12,”>”& 4).Īs well as this =COUNTIF(B3:B12, E2), (where E2 has the text >4) and even this =COUNTIF(B3:B12, criteria1), (where E2 and is a named range called criteria1 and has the text >4).Īs per function, this too works over a range of columns if you needed it to. We also need to use the “ &” symbol to join the text. We need to put > into “quotation” marks because it’s text. This number can be entered directly into the formula or referred to on the worksheet.īelow we select our array B3:B12 and ask it to COUNT the number of times that it finds a number GREATER THAN the value found in cell E2, in this case, it’s 4. This number represents the boundary above which you want to count. The Excel COUNTIF GREATER THAN function is basically using this function and the ‘>’ symbol (GREATER THAN) as your criteria combined with a number value. The result now calculates 6 times that 1 appears. This also works over a range of columns if you needed it to. Alternatively, there is a COUNTBLANK function, if you wanted to count blank cells in a range.Īs per Excel, it defines this as “the condition in the form of a number, expression, or text that defines which cells will be counted.”This is where the magic happens, we’ll look at the example below but you can ask to find the number of times “apples” appear.īefore we look at several examples let’s look at one basic version.īelow we select our array B3:B7 and ask it to COUNT the number of times that 1 appears. COUNTIF Syntax ExplainedĪ small function in COUNTIF in terms of arguments is required but the criteria element can be so many things, so let’s look at several.Īs per COUNTIF function Excel, it defines this as “the range of cells from which you wish to count nonblank cells”. Strictly speaking, you can use this function to count blank cells if you want to. This can be a number of different conditions, from matching text, numbers, cells containing text, matching part text, and more. It looks in a given range and counts the number of times a cell meets a given condition. What is COUNTIF in Excel?īriefly, this function is a more diverse version of COUNT. So, by the end of this article, I hope you will learn how and where to use these conditional counting functions in your daily work. Besides, we will be able to count cells with values that meet a certain condition specified in the formula. With these functions solely or in collaboration with other functions, we will be able to count the number of cells that contain certain texts, numbers, count blank cells, and non-blank cells. We all know that Excel has a variety of built-in functions, but did you know that the most used functions in Excel are the functions that count and sum? So today, I am happy to discuss and explain (the best I could!) these functions under the statistical category specifically those used for conditional counting – COUNTIF, COUNTIFS, and COUNTBLANK. Excel COUNTIF Function (Introduction to Conditional Counting)