Then, enter the SUMIF formula, specifying the range, criteria, and sum range. However, I have an additional requirement related to the above workflow. How to Use Excel SUMIF () with Greater Than. Step 1: Select an empty cell. Click Kutools > Super LOOKUP > LOOKUP and Sum. 1. The syntax of the SUMIF function is as follows: =SUMIF (range,criteria, [sum_range]) In the SUMIF function, the range argument specifies the range of cells that you want Excel to evaluate. The SUMIFS Function sums data rows that meet certain criteria. What we need to do now is to find the total number of hours that were worked. I want to add all the prices in another sheet (logs) if the dates in the rows are in between the date in B2 and B3. Summing Up Pseudo Blank Cells Using Trim Function and Helper Column. Calculate Hours with a Simple Formula. To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Example: SUMIFS (A5:L5,A3:L3,"=2010",A2:L2,"=Ordinary") If you are having trouble with merged cells just un-merge them, or try using named ranges (under. Now, enter the starting parentheses. Incidentally, you can likely make the formula approach somewhat faster by having it consider only the used parts of columns A and B, rather than the entire columns. The range argument is the range of cells where I want to look for the criteria, A2:A19. The actual cells to add, if you want to add cells other than those specified in the range argument. The Excel SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. Pada tutorial ini, kamu akan mempelajari rumus SUMIF excel secara lengkap, mulai dari prinsip dasar penggunaannya sampai ke pemakaian tingkat lanjutnya. To achieve the result you want, you need appropriate formats for the time values, one for the recorded times and another for the total: If you input a time spent by typing 3:15 (three hours and 15 minutes), then it’s likely that Excel will have. SUM Not Working with Time Values in Excel: 5 Solutions. The range of cells that you want evaluated by criteria. In our demonstration, both A1 and B1 are automatically formatted as time. 0. criteria-Selected criteria for the range. The function returns the SUM of the time values only which satisfies all the criteria in the formula. Click GO ADVANCED and use the paperclip icon to post up your workbook showing the misbehaving data so we can look directly. Re: Need help; can't SUM hh:mm:ss. H6 = the number of earlier days. It sums the data in a column or row based on a condition. What we need to do now is to find the total number of hours that were worked. The WorksheetFunction object can be used to call most of the Excel functions that are available within the Insert Function dialog box in Excel. 09-04-2014, 01:15 AM #12. This will return the sum of the values within a desired range of cells that all meet one criterion. But how to set the correct time format? Well, it’s easy. Knowing this, you can simply subtract the start time (“Clock In”) from the end time (“Clock Out”) to find the time worked. If I use =SUMIFS. SUMIF formula to find sum for dates today or earlier. Each video comes with its own practice worksheet. Insert the criteria to be used in the SUMIF formula. Using Excel Formula to Sum Time in Excel 2. I tried sumif but the problem is that I can't figure out how to include the values for lines as 00:00 to 00:00 which means this contract runs for 24 hours and also contract starting at 23:00 preceding day till 05:00. 4. B2 = -A1. Select the range to compare against the criteria. You can also add up times by using AutoSum to sum numbers. [Column A] multiple duplicate staff names (format text) [Column B] Start time (format hh:mm) [Column C] End time (format hh:mm) [Column D] =C-B for total hours worked (format hh:mm)I may be wrong, but I believe the cause of your problem is that excel is treating 11:45 PM and 12:44 AM as being on the same day. Steps: Firstly, you have to select a different cell E5 where you want to see the result. What formula can I use. In cell C2, enter the end time, including “ a ” or “ p ” as appropriate, and. Excel Formula SUMIFS on date and time using date from given cell. Using the SUM Formula Using The AutoSum Function Instead of inserting the SUM formula, we can also use the AutoSum function to add up the time values in Excel. We can use both Excel SUMIF and SUMIFS functions to sum values based on date criteria. 1. The following SUMIF function gives the exact same result. The Not Blank operator can be used to find out whether a cell or a range of cells is blank or not. --2) Sum a simple range of cells. I use SUMIFS instead because I know I can easily modify it if additional conditions come up over time. As shown in the example, you can also add a helper column to calculate and sum time deltas. 0. The total sum of your hours should now show up!Suppose you want to sum orders’ amounts for either of the products “Orange” and “Apple” supplied as criteria in array constant then you need to provide multiple criteria in SUMIFS function as follows; =SUM (SUMIFS (D2:D22,B2:B22, {"Orange","Apple"})) Remember, you cannot use an expression or cell reference an array constant. And here are some important notes when using these functions: You can use either SUMIF or SUMIFS if you want to sum by a single criterion. 1. Technology for Teachers and S…Subtract time In cell B2, enter the start time and include “ a ” for AM or “ p ” for PM, and then press Enter. Excel time values are actually numbers and you can sum them up using the SUMIF function like other numeric values. We use asterisk at the start and end of the specific word to. 0. Get the SUM of Time Value Inserting Individual Cells. Enter the formula =SUMIF (C3:C191, "Ag", D3:D191). COUNTIFS applies criteria to cells across multiple ranges and counts the number of times all criteria are met. Your data is being identified as text and excel won't sum text. Remove any ticks from the Delimiters boxes and click Next. Earlier we used =SUMIF (A2:A7, "Biscuits", B2:B7) to get the total cost of Biscuits. Example: Apply SUMIFS Function to Get Summation Based on Multiple Criteria in Excel. It is used to scan through a range of cells checking for a specific criterion, and then adding up values in a range that correspond to those values. Go to Home > Format, and then choose Format Cells. But how to set the correct time format? Well, it’s easy. 1. Here is an example: =SUMIF (A3:A10. Overview: 7 Ways to Sum in Excel. Fig: Time function in Excel. It updates the SUM when values inside of the range are changed. According to this formula 19:00 hours + 11:00 hours equals 6:00 hours. Kamu bisa saja sering memanfaatkan fungsinya, tergantung dari jenis pemrosesan angka yang perlu. After 2007, the SUMIFS function was created which allows a. Type the formula given below:Ketika bekerja dengan Excel, fungsi SUMIF dapat menjadi alat yang ampuh untuk melakukan perhitungan dan operasi berdasarkan beberapa kriteria. Then a LOOKUP and Sum dialog box pops up, you need to finish the below settings:. How to use SUMIF(S) in Excel with date criteria. Nov 26, 2020. Now I need to somehow sum up rows (formated as time) and. Sum Time Applying the AutoSum Tool. The difference is that the SUMIF function sums only those values that meet the criteria you specify. To use Excel SUMIF with a date range, the criteria need to be constructed with logical operators. The formula in cell H5, copied down, is: =SUM (C5:G5)When Microsoft Excel recalculates a worksheet that contains a SUMIF formula, the recalculation takes longer to finish than expected. Syntax of SUMIFS with OR logic. See screenshot: Hello All, I'm trying to see if it's possible to use the Countif and Sumif formula between 2 date/time ranges without having to manually type in the date and time. However, you say that =A1+A2+A3. Here, we will see how to use the SUM and TEXT functions together to add time over 24 hours. I'm using the formula =SUMIF (Calls!C:C,Report!A4,Calls!K:K). . Let’s say, you need to find the sum of sales revenue in a particular region, you may use SUMIF. First, highlight the cells you want to sum. The SUMIF function is a premade function in Excel, which calculates the sum of values in a range based on a true or false condition. Step 4: In the “data validation” dialog box, select “list” under “allow” criteria. 1. Now, your SUMIF () should work perfectly. 5 hours, we get the above. ; Secondly, you should use the corresponding formula. =text (F13-D13,"h:mm") and use simple subtraction instead=F13-D13. Make sure the selected range (s) contains numerical data. The syntax, or the structure of the formula, is =SUMIF (range,criteria,sum_range). 1. If it isn't, performance may. 15. Click in cell B4. To do this, we just need a simple sum formula: But, since the total number of hours is greater than 24, by 7. The Excel SUMIF () and SUMIFS () functions can be used to add values based on a date range, such as between two dates, before a date, and after a date. If the sum_range and criteria_range aren't the same size it just extends the sum_range out to match. SumIfs based on Range that has date and time. The format of a cell per se does not matter. For example, I can use 01 Jan, 2020 or 01 January 2020 or 1-Jan-2020. Sum overlapping time. The formula in cell H8, copied down, is: = SUMIF ( data [ Name],G8, data [ Hours]) The range is the "Name" column of the table, the criteria is the value from G8 ("Jane"), and the sum_range is the "Hours" column. Select the Cell for the Total: Choose the cell where you want the total hours to appear. The biggest problem people encounter when they try to add time values is incorrect formatting of the results cell. Rows and columns should be the same in the criteria_range argument and the sum_range argument. The SUMIF function determines which cells in the range from B2 to B16 contain a date that is less than today’s date minus twelve months. B1:B19 = 10. 2. After that, in the second augment, you need to use the VLOOKUP function to lookup for the product ID by using the product name from the cell above. Total Sales Measure. I'm trying to add up a range of times using SUM but I always get the time of 0:00, 00:00, or 00:00:00 etc etc. The Excel SUMIF function will then sum only the cells that. Write the value of the first criterion. Combine Other Excel Functions with SUMIFS to Match Multiple Horizontal and Vertical Criteria. Functions & Formulas. SUMIFS on the other. But both can be combined for common cause. Additionally, you can use SUMIFS over SUMIF to add up numbers based on multiple. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar value. If that is correct, that would indicate that A1, A2, A3 et al are not text. SUMIF adalah salah satu dari beberapa rumus penjumlahan yang bisa kamu gunakan di excel. Share. Combine SUMIF and EDATE to select dates based on age. The Excel SUMIF function isn't just for simple calculations; it can also handle more complex scenarios with multiple criteria. Solution: Define Criteria Correctly. The generic syntax for SUMIF looks like this: = SUMIF ( range, criteria, sum_range) For example, to sum values in D5:D16 that are greater than $1,000, we can use the SUMIF function like this: = SUMIF (D5:D16,">1000") // returns 7400. It will replace End Times with 17:00 and Start Times with 09:00 if the End Time is later than 5 PM or the Start Time is earlier than 9 AM. Apply [H]:MM Time Format to cells, before you apply Formula to get Time Difference. Go to the Home tab, click the Cells group, and then choose the Format and format cells afterward. 1. --3) Running Total. sum_range – The range to sum. sum_range Optional. Summation Between Two Dates. Step 1: Write =SUMIF and double-click to select SUMIF. The following criteria are entered on the worksheet, and referenced in the formulas: Start Date - cell D2. The SUMIF function is relevant when you want Excel to sum up values based on a single condition only. SUM If Various Text Criteria Appear in Excel. For example, you would use SUMIFS to sum the. After that, the Edit Name dialog box will pop up on the screen. By default when you SUM time in Excel, it counts time of day + time of day. Enter the following formula, replacing the cell references and criteria with your own. Whether you’re analyzing sales data, tracking project milestones, or monitoring expenses over time, SUMIFS can simplify your data calculations. Then, we will take that to the next level and learn how to use multiple sum_ranges in the conditional sum. Apply the SUM Function to Find the Time Value. Here the SUMIFS function satisfies all the conditions mentioned. Creating a running total by using the SUM function is pretty similar to using simple addition. Explanation: This SUMIF function, in the worksheet below, sums the prices in B2:B7 (range-to-sum) whose product IDs in A2:A7 (range) start with the letter B (criteria). In the end, enter the closing parentheses and hit enter to get the result. It is used to scan through a range of cells checking for a specific criterion, and then adding up values in a range that correspond to those values. The SUMIF function is a built-in feature of Excel utilized to calculate the sum of values in a range based on a “TRUE” or “FALSE” condition. SUMIFS function. In the 1st step, simply mark Delimited. so you can learn Excel in less time, and easily review key topics when needed. Enter one duration in cell B2 (with the format h:mm) and hit Enter. The criteria are based on dates, numbers, and text. In that case 9:00 + 11:00 would be displayed as 20:00. In our formula, we take the initial time in cell A2, and then using the TIME Function we enter the hours from B2. Here's an example of the data entered. 💥 Formula Breakdown. Here, the function will sum values from the Sum_Range E5:E14. If you have more than one type of income that fits into a specific. The following SUMIF function gives the exact same result. The entries represent the hours and minutes works (e. This tutorial demonstrates how to use Excel SUMIF with an easy to follow example and takes you step-b. Date_Time Total. I’ll guide you through this tutorial which will show you how to sum time in Excel. Looking at the image of Formula AutoComplete and the list of arguments, in our example sum_range is D2:D11, the. sum_range-Range of cells that are considered for summing up. First, select cell E6. First, we will see how to use LET to assign names to the SUMIF range, criterion, and sum_range parameters. Ability to use criteria with logical operators, like less than (<), allows adding values between other values. Getting SUMIFS to work in array with. How to include cells with today date in. 2. It is typed =SUMIF: =SUMIF ( range, criteria,. More criteria can be added, if needed, and this example shows totals in a date range for a specific region. Introduction to SUMIF Function in Excel. Lets change Rattata stats to a negative number to see what happens. Report abuse. On the flip side, you can add numbers that are. Select the cell where you want your result and enter the following formula, replacing the cell references with your own. Supposing you have a few project times in column B and you want to add them up. While enter Time in cells, the Time must according to 24 Hrs Clock, for example 15:30 for 03:30 PM, since cells has TIME Format HH:MM AM/PM. So the formula would be: =SUMIF(name_column,"Puneet*~",amount_column) So when you use a. Fig: Sumif function in Excel. So if I try to do a sumifs where the hour is >=22:00 and <23:00, the formula works. Figure 1. Step 1: Write =SUMIF and double-click to select SUMIF. A new window will pop up named New Name. Excel sumif with both horizontal and vertical criteria's. Hit Enter to. Click the Data button on The Ribbon. I need to know what is the sum value between 00:00 and 01:00 (which is 8). With sumif, you can sum cells that meet a certain criteria. , A1: 22 hours 25 minutes) So now all I want is to calculate total working hours like =SUM A1+A2+A3+A4= +-total working hours. Make sure THAT column has the same data type as K16. You could take the previous example where there are 3 columns, one to place the items, another to indicate the sales, and the last to show the days of product delivery. In this Video Tutorial, I will show you how you can sum and count between two times in Excel, or how to sum and count during a certain time interval. I have these 2 time stamps. Here I would like to show sum examples. Now, we need to calculate the sum of X sales in all three companies, A, B, and C. =SUMIF (A$1:A$10,"X",B1) The referenced range argument is 10 rows so the function will automatically evaluate the sum_range argument to be the same size and starting from the first cell used as the sum_range argument even if the sum_range argument isn't. _________________. Most of the time, the problem you will need to solve will be more complex than a simple. I am using sumif all the time with pounds - I have to use with time but the results aren't right. The sum_range is the range I want to sum, D2:D19. The formula is explained in the next section. For example, the A1:A20 range. To sum values within a certain date range, use a SUMIFS formula with start and end dates as criteria. Select a blank cell, enter below formula, and press the Enter key. Note: these are volatile functions and will recalculate with every worksheet change. 0. The steps are given below. please suggest or help me to solve this. For each call to the Excel Sumif function, the range argument (to be tested against the criteria ) is either the cell range A2-A9 or the cell range B2-B9, and the [sum_range] argument (containing the values to be summed) is the cell range C2-C9. After that, refer to the date column, range A2:A15. criteria_range_n, criteria_n]) {n<=127} I can't think of a way to do the Month() comparison within the SUMIFS statement, but you could insert a helper. However, the date & time are combined into 1 column (ex. Technically, the NOW function returns the current date and time, but you can format as time only, as seen below: TODAY () // returns current date NOW () // returns current time. Or you can use this way: How to mark a thread Solved. To calculate the sum based on criteria, start by selecting a cell where the result will be displayed. Steps: First, select all the cells of the column named Total. 1) In the Lookup and Sum Type section, select Lookup and sum matched values(s) in row(s) option;; 2. When Excel recalculates the worksheet, Excel takes longer to move the insertion point to the next cell in the. It allows you to Sum the values in a range that meet a criteria that you specify. In the Type box, at the top of the list of formats, type [h]:mm;@ and then select OK. Use SUM with If Condition For Different Comparison Criteria in Excel. 3. Let’s see these steps in detail: 1] Launch Microsoft Excel and open your spreadsheet in it. The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria. Type D7(-350):. The syntax determines the criteria for adding up numbers, while the usage specifies the range of numbers to be added. Right-click and pick "Format Cells," or go to Home > Format > Format Cells. What we want is to count AMOUNT of time. From that box: Click on New. So, in this example, select cell E4. For instance, let's say you want to calculate the individual monthly hours worked for all your clients. Then, the string “Online” refers to the criteria argument to apply within the given range. Pokud je argument součet vynechán, sečte aplikace Excel buňky, které jsou zadané v argumentu oblast (stejné buňky, na které je použito zadané kritérium). For example, you type a number into a cell in the worksheet. You can use the Excel SUMIF Function to calculate the monthly sum of hours worked for client projects. We will apply the SUMIF formula in cell I7 to get Mexico’s total or gross sales. 1. Understanding the Syntax of the Sumif Function in Excel. 2) In the Select Range section, specify the Lookup Value, Output Range as well as the Data table. 2. The sheet has daily hours calculated for a two week period, with subtotals for each week. ms office formula gives 0. In the Format Cells window, on the Number tab, choose "Time" as the Category. The sheet 'Calls' is data from our phone system. Solution 2: Apply Text to Columns Wizard. Criteria_range1 (required) - the range of cells to be evaluated by criterion1. The brackets ensure that Excel no longer suppresses the hours that go beyond a day. In the example shown, the formula in G5 is: =SUMIFS(times,states,F5) where times (C5:C15), and states (D5:D15) are named ranges. 0. STEP 1: Enter the SUMIFS function. The result is 28 hours and 15 minutes. I tried sumif but the problem is that I can't figure out how to include the values for lines as 00:00 to 00:00 which means this contract runs for 24 hours and also contract starting at 23:00 preceding day till 05:00. 1. =SUMIFS (C2:C10,A2:A10,"=",B2:B10,"=") The above formula is showing 0 since there is no blank cells in column A & B. (Note, we multiply by 24 to convert the decimal values into hours). I have previously had to complete this task with reference to a single date (e. Example 4: Criteria with Cell Reference. Worked like a champ =SUMIFS('Monthly Expenses'!D4:D27,'Monthly Expenses'!C4:C27,Values!E1) +SUMIFS('Monthly. To do this, we use a CALCULATE statement. Then on the HOME tab, click AutoSum. In Method A, eight and a half hours will appear as 8:30 In Method B, eight and a half hours will appear as 8. . Go to Home > Format, and then choose Format Cells. SUMIF function in Excel sums the values in a range that meet criteria that you specify. 8/29/2006 9:59 21. How to sumifs based on date and time in excel? 1. the date time is in this format: 2013-09-16 04:00. When counting minutes and seconds, enter the info as 00:mm:ss and format it as mm:ss. I can achieve this by using SUMPRODUCT but it takes a long time to process as my data is more than 10K rows. After you type =SUMIFS (, Formula AutoComplete appears beneath the formula, with the list of arguments in their proper order. 4/21/2014 13:30). Solution 4: Use the VALUE Function. 50, which is the total amount associated with financial reporting audits. Each row shows the Rolling Total for. And here are some important notes when using these functions: You can use either SUMIF or SUMIFS if you want to sum by a single criterion. The function is available in Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower. Remember: SUMIFS will return a numeric value. To sum values based on blank cells, please apply the SUMIF function, the generic syntax is: =SUMIF (range, “”, sum_range) range: The range of cells that contain blank cells; “”: The double quotes represent a blank cell; sum_range: The range of cells you want to sum from. As the formula is copied down, it returns a count of each color listed in column F. We generally use SUMIFS t. Scroll through the options under the section ‘Type’ and select the formatting option where the letter ‘h’ is in square brackets. 17. The difference is that the SUMIF function sums only those values that meet the criteria you specify. In this step-by-step tutorial, learn how to use the SUMIF Function in Microsoft Excel. Here the arguments to the function is given as cell references and named ranges. In the Type box, at the top of the list of formats, type [h]:mm;@ and press OK. This can be achieved by the help of the formula – TODAY (). To add time values that exceed 24 hours, you should format the cell to accommodate longer durations. 2. The actual cells to add, if you want to add cells other than those specified in the range argument. In the yellow cells I am trying to caluclate sumif of values in range B9:B14. SUMIFS with Or. Step 3: Click OK. SUMIFS is an Excel function used to find conditional sums with multiple conditions. Next, Insert the following formula: How to sum time in Excel. = List. The SUMIF function adds only the values that meet a single criteria. If you want to add numbers with the parameter equal to, you should use the formula =SUMIF (C2:C10, 3, B2:B10) or also =SUMIF (C2:C10, =3, B2:B10). Sum time over 30 minutes. How to Sum Time in Excel - Microsoft Excel Video. =SUMIF(B8:B20,"<=HOUR(time())", D8:D20) so if it is 10:00 AM the sum in cell D21 should be 24. Every day since 1900/01/00 (arbitrary chosen date) are indexed. In the Type box, at the top of the list of formats, type [h]:mm;@ and then select OK. 0423726851851852 after clearing the #s number format. Click Finish. This short tutorial shows how a usual Excel Sum formula with a clever use of absolute and relative cell references can quickly calculate a running total in your worksheet. The SUMIFS function adds only the values that meet multiple criteria. With your blinking cursor. The & operator joins the 'less than or equal to' symbol and the value in cell C1. Next: Array formulas. =SUMIF (C2:C7,">25000") This formula adds the numbers in the cell range C2 through C7 only if they are greater than 25,000. =SUM(IF( (A1:A10>=1)* (A1:A10<=10),B1:B10,0)) You can implement an OR in a SUM+IF statement similarly. ]) Where: Sum_range (required) - the range to be summed. excel how to calculate sum of time hot to do a times sum in excel how do find the sum of time in excel how do i sum time in excel ? how do you format a cell in excel to sum time youtube how do you sum time format in excel how do you sum time in excel how do you sum time. Those who are familiar with the SUMIF function might think that converting it to SUMIFS takes just an extra "S" and a few additional criteria. png thank youThe problem is that I the cells to be matched can occur multiple times and basing on the Mapping legend they should return the sum of values. And even if you do they seem to work differently from one formula to another! A few weeks ago Dave wrote to me as he was having trouble getting a SUMIFS formula to correctly use dates referenced in its criteria. Have you looked at SUMIFS (), its for doing SUMIF () with multiple criteria. 1. sum_range Optional. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied). 0. The first range (C3:C191) is the range we want to test against the criterion ("Ag"). It's one of the most useful functions in Excel. Overview: 7 Ways to Sum in Excel. Advanced IF functions. SUMIF may not visibly work when you try to sum up times. If you want a static value, use date and time shortcuts. 50. With sumif, you can sum cells that meet a certain criteria. Add the range you want to use as criteria as the first argument of the SUMIF function. Plus symbol (+) is used as the OR operator. In the LOOKUP and Sum dialog box, please do the following operations:. This criteria finds the smallest value in the range and compares that value to each value in the first argument. View Paid Training & Bundles. I have seen many ways to determine overlappping times, notably using SUMPRODUCT. _________________. Learn. Here I want the sum of sales value for the Apparel column containing “ PANT ” text. If you’re new to Excel, SUMIF might seem daunting at first. In this Excel formulas an function tutorial, you'll learn how to sum values based on MULTIPLE criteria that are in the SAME column. In my result, its calculated as hh and mm. Format the cell. In Excel, the SUMIF function can help to sum cells based on one criterion, this article will explain this function's syntax and introduce some examples for using the SUMIF function. . Let’s take an example to understand it. This is so that the value in B2 can be converted to the properly formatted decimal number. Table 2 = Date, Time, Event. Press Enter to get the result, 16 hours and 15 minutes. Step 2: Now, select the range B7:B24 and put a comma to separate it from the criteria. Range: The range of cells which included the criteria; Criteria: The condition that must be satisfied; Sum range: The range of cells to add if the condition is satisfied. I have tried SUMIF and SUMIFS. =SUMIFS ( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]. For instance, you can use ">" and "" to specify a date range. For a quick overview of 7 different ways to sum numbers with Excel functions, you can watch this 1-minute video. Each task has a separate row and each task has a specific duration. Method 4: Adding Time in Excel Over 24 Hours Using TEXT & SUM Function Together. You could simply use a wildcard (an asterisk, *, is a wildcard in Excel) in your COUNTIF formula like this: =COUNTIF (A5:A9,"*apples*") Your result will be 4.