For example, here, I will check the time based on 3:30PM, if the time is greater than 3:30PM, a text “Yes” is displayed, if less than 3:30PM, a text “No” is appeared as following screenshot shown. Right-click on the cell, then click on format cells. We will apply the SUMIF formula in cell I7 to get Mexico’s total or gross sales. [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. Here I have time in HH:MM:SS format. Key Takeaways. The SUMIFS Function sums data rows that meet certain criteria. Now we will use the following formula to get the count of times which lays in between 9:00 to 10:30 as given time values. You can leave C1 as a date and the data as it is - just change your formula to the following. The result will be recorded in column E. The biggest problem people encounter when they try to add time values is incorrect formatting of the results cell. It is typed =SUMIF: =SUMIF ( range, criteria, [sum_range] ) The condition is referred to as criteria, which can check things like: If a number is greater than another number >. I'm using the formula =SUMIF (Calls!C:C,Report!A4,Calls!K:K). The SUMIF and COUNTIF functions allow you to conditionally sum or count cells based on a single condition, and are compatible with almost all versions of Excel: = SUMIF (criteria_range,. Report a problem with this page. its timesheets time: Sheet 1. The COUNTIF function counts only the values that meet a single criteria=SUMIF(B2:B19,D2,C2:C19) It keeps the formula tight but people might have trouble reading the criteria. In the example above, we used the following formula: =SUMIF (C3:C13, ">"&F2) The formula adds up the values in the range C3:C13 if they are greater than the value in cell F2 (which, in this case, is 2500). read more. 8/29/2006 9:41 5. After that, the steps are the same for both methods; click Custom in the category. The SUM is updated to D12(2489). First, let’s cover the SUMIF function. The SUMIFS() function adds the cells specified by a given set of conditions or. Figure 1. The “<>” symbol is used to specify the values that are not blank. #1. 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. A small dialog box will pop asking you to select a cell with the sample color. To define criteria, SUMIFS supports logical operators (>,. Steps: Firstly, you have to select a different cell E5 where you want to see the result. The first expression subtracts 30 minutes from every time in the named range "times": times. To do this, use the EDATE and TODAY functions. Calculate Total Minutes Using SUMThen that "time" just converted to percentage. Sum_Range is entered last in the SUMIF functio n. Sum time over 30 minutes. You can't format -ve Date/Time values so not sure how you managed to format with hh:mm:ss, a -ve time will give you a series of #s, so I wonder if your values are correct. We can use both Excel SUMIF and SUMIFS functions to sum values based on date criteria. The function calculates the sum for the dates which are 10 days earlier from today’s date 25-Oct-22 which are: 300, 500, 100, 500, 800, 250, 500. 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. Figure 1. Looking at the image of Formula AutoComplete and the list of arguments, in our example sum_range is D2:D11, the. 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. Using The SUM Function to Sum Time in Excel. Using three sets of range and criteria in Excel SUMIFS. We may need to extract the sum for multiple criteria that are impossible with only one use of the SUMIFS function. 10. TIME() The TIME() function converts hours, minutes, seconds given as numbers to an Excel serial number, formatted with a time format. Enter this formula somewhere and copy down 10 rows. In the following dataset, we have. SUMIF adds all your values as well. Click here to download the sample Excel file. =SUM (B2:C2) Figure 2. Here's the formula I'll use: =COUNTIF (C2:C17,">100") This is a simple, two part formula: simply point Excel to the list of data to count, and the rule to count. I do understand why but I'm struggling to find an alternative way to solve this task. You can do something like this. Once the dialog box opens, you will see 3 steps. 13 hours ago · SUMIF works well but I can't figure out a way to do it well across multiple columns. 0. The Excel SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. 15. I think the ones that do not are due to the time being 00:00? I want to use a SUMIF looking at that column, but looking only at the dates. Do the same in cell C47 to calculate the total amount for the IT audit. Method 1: Use SUMIFS Function to Sum Between Two Dates I’ll use the SUMIFS function to find the total sales between two dates. In Excel, you can sum up time first, and then format the result as you need. For example, the simplest SUMIF formula =SUMIF (A2:A10,">10") adds the values in cells A2 to A10 that are larger than 10. After you type =SUMIFS (, Formula AutoComplete appears beneath the formula, with the list of arguments in their proper order. For example, Column A has bunch of dates (range name 'Date'), Column B has time (range name 'Time') and Column C has volume (range name 'Volume'). As long as the date format is valid, Excel will be able to use it to calculate the sum. It allows you to Sum the values in a range that meet a criteria that you specify. You can also add up times by using AutoSum to sum numbers. Pada tutorial ini, kamu akan mempelajari rumus SUMIF excel secara lengkap, mulai dari prinsip dasar penggunaannya sampai ke pemakaian tingkat lanjutnya. If you’re new to Excel, SUMIF might seem daunting at first. The formula SUM s up the values of a range of cells, IF certain criteria is met. The criteria can be within the range of values itself, or in a different range that is the same size as the values range. Example 1: Using SUMIF. SUMIF formula to find sum for dates today or earlier. I have sample data consisting of the number of phone calls received on 5/5/2013 between 12:00 AM and 6:00 AM. SUMIFS with Or. 0. Only one column must be selected. But how to set the correct time format? Well, it’s easy. 1️⃣ However, if you want your dataset to be checked against multiple conditions before the sum is performed, use the SUMIFS function. As you type the SUMIFS function in Excel, if you don’t remember the arguments, help is ready at hand. In this example the correct value would be 2100. Rattata total stats was changed from (D7)253 to (D7)350. Table of Contents hide. You want the sum_range to be the same for all 5 columns. The Excel SUMIF function will then sum only the cells that. Step 2: In the Data tab, go to the “data tools” section. In the first example we're using =((B2-A2)+(D2-C2))*24 to get the sum of. e. I use SUMIFS instead because I know I can easily modify it if additional conditions come up over time. Excel time values are actually numbers and you can sum them up using the SUMIF function like other numeric values. Suppose you want to sum up the time difference between your team members working on multiple project tasks. Excel SUMIF Function Condition with Numerous Comparison Operators & Cell Reference. The syntax, or the structure of the formula, is =SUMIF (range,criteria,sum_range). SUMIF with Question Mark. Use of SUMPRODUCT If SUMIF Not Working Anyway. First, select cell E6. 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. Creating a running total by using the SUM function is pretty similar to using simple addition. The SUMIF function is one of the older functions used in spreadsheets. 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. E. 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. Select the cell where you want your result and enter the following formula, replacing the cell references with your own. The function resides in cell C3. This is so that the value in B2 can be converted to the properly formatted decimal number. Task 3: 12:00 - 22:00. =SUMIF (B2:B14,”*PANT*”,C2:C14) The SUMIF function in the sums mentioned above OR adds up the range C2 to C14 if its corresponding or neighbor cells contain the keyword “PANT” in the range B2 to. For a quick overview of 7 different ways to sum numbers with Excel functions, you can watch this 1-minute video. I need to sum the tips for each hour of the day starting at 5AM. --3) Running Total. Excel: sum values of a column based on multiple options' values of multiple columns. For example, we want to evaluate the sum of total sales for all notebooks that originated in the USA and. the date time is in this format: 2013-09-16 04:00. 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. Let’s see these steps in detail: 1] Launch Microsoft Excel and open your spreadsheet in it. What say you wanted to count the number of cells containing the word ‘apple’ in this table. =C2-B2. SUMIF and SUMIFS functions can be used to sum values that meet a criteria. Skip to main content. At the same time, the SUMIFS function is a relatively newer function (introduced with Excel 2007) that extends the abilities of a SUMIF function by giving you the facility to add only those items that meet a set of criteria. As you type the SUMIFS function in Excel, if you don’t remember the arguments, help is ready at hand. 1) In the Lookup and Sum Type section, select Lookup and sum matched values(s) in row(s) option;; 2. Do this and click OK. The Not Blank operator can be used to find out whether a cell or a range of cells is blank or not. Select the cells in column F containing “time values”. sum_range Optional. 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. This example will use three search criteria. The SUMIF function is a worksheet function that adds all numbers in a range of cells based on one criteria. First, click on cell H5 and type the following formula. The blue links go to the written steps for each example. I have tried: =SUMIF. SUM Not Working with Time Values in Excel: 5 Solutions. I. The data is already time/date stamped so in another column i do a simple =Text(A2,"HH:MM") to turn it into a time. However, see below for a case-sensitive option. 3. =(D3-C3)*24. excel SUMIFS only on same. Then, we will take that to the next level and learn how to use multiple sum_ranges in the conditional sum. I then just want to sum all the volume that is between the times. Applying The AutoSum Option to Add Time in Excel 4. After you type =SUMIFS (, Formula AutoComplete appears beneath the formula, with the list of arguments in their proper order. Click on “Cells” and under the “Number” tab, choose “Time”. Click to read our step by step guide on sumif excel. For example, if you use a formula like SUBTOTAL (109, A1:E1) to sum numbers in a. Using SUM Function to Add Hours and Minutes in Excel. I'm trying to find an average score based on certain criteria. In this case, we're checking cells C2 to C17, for all values greater than $100. SUMIFS is an Excel function used to find conditional sums with multiple conditions. That is the sales column. Every day since 1900/01/00 (arbitrary chosen date) are indexed. The & operator joins the 'less than or equal to' symbol and the value in cell C1. It will sum up cells that meet the given criteria. Introduction to SUMIFS with date range. N. Share. Importing And Cleaning Data In Excel. sum_range – The range to sum. A date is a whole number between 1 (01 Jan 1900) and 2,958,465 (31 Dec 9999), but shown in a way that looks like a date. Then on the HOME tab, click AutoSum. . Kamu bisa saja sering memanfaatkan fungsinya, tergantung dari jenis pemrosesan angka yang perlu. If that is correct, that would indicate that A1, A2, A3 et al are not text. 1. Try this formulas: To convert a time in the form of text to a serial number use this formula: = TIMEVALUE (D12) To add time entered as a number use this formula: =SUM (E12:E22) and format the cell as: [hh]:mm:ss. If the sumif isn't adding properly, it's a problem in column F. The yellow marked boxes indicates the named ranges used. In this section,. When you use the plus sign, that forces Excel to recognize it as numbers. ) The conditions are referred to as criteria1, criteria2, and so on, which can check things like: The criteria_range1, 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). Using Excel Formula to Sum Time in Excel 2. 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. In cell F3, the blank cell formula is. This can be achieved by the help of the formula – TODAY (). The method shown above counts the number of cells in the range A1:A10 for which both tests evaluate to TRUE. Instead, you’d have to move to a more advanced version of the Excel SUMIF function – the SUMIFS function. In Excel this would look like. This guide to the SUMIF Excel function will show you how to use it, step-by-step. Remember Excel stores dates and times as numbers. The SUMIF function is an older function that supports only a single condition. , 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. Given the data table with order numbers and the bill amount. SUMIFS on a range of data. SUMIFS allows you to sum values in a range that meets multiple conditions or criteria. View Paid Training & Bundles. For example in the table below, for each row I would like column D to display the sum of column C for. I am taking the sum of a range of cells where a SUMIF condition is met. After you type =SUMIFS (, Formula AutoComplete appears beneath the formula, with the list of arguments in their proper order. 2. 0. So my sloppy solution is just to do the regular SUMIF 5 times. The second. Or you can use this way: How to mark a thread Solved. Fig: Year function in Excel. Working With Text Conditions. = List. . In Excel, how can I use the SUMIFS function to sum values that occur prior to a certain date. Pivot Tables. Here’s an example:状況 30分以上の合計時間を計算したい 作業に30分以上かかっている人の、その超過分だけ計算したいのですが。。。 関数例 = sumproduct（（範囲- time（0 、30 、0 ））* （範囲> time（0 、30 、0 ））） 説明 期間を表す一連の時間を指定して、30分間の合計時間を合計するには、sump…Use the SUMIF function in Excel to sum cells based on numbers that meet specific criteria. For example, I want to calculate sales between (7/7/2014 4:00:00) and (7/8/2014 3:59:59). Go to the first post. One produces count as well as other sums. For example, with dates in b5:b15, a cutoff date in b2, and the amounts I want to sum in c5:c15, I can use =SUMIF(b5:b15,b2,c5:c15) to sum amounts that occur on the date specified in b2. so you can learn Excel in less time, and easily review key topics when needed. H6 = the number of earlier days. The second will be the range/column to fetch the region you want to filter out. Regardless of how the cell is formatted to display, a date/time cell is actually a decimal value. SUM will ignore text and is why it will return 0. Now, we need to calculate the sum of X sales in all three companies, A, B, and C. 3. The times are in UTC, but I want to generate a date range to give me data based on my time zone (+5). You can always ask an expert in the Excel Tech Community or get support in Communities. Use formulas of the form =ISTEXT (A1) to determine if the type of the cell value is text. Table 2 = Date, Time, Event. Scroll through the options under the section ‘Type’ and select the formatting option where the letter ‘h’ is in square brackets. 2. For example, you would use SUMIFS to sum the. 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. The specified criteria can be a logical condition using numeric values, as well as dates and text, where the calculation only includes the value of the cells within the selected range that meet the. When counting minutes and seconds, enter the info as 00:mm:ss and format it as mm:ss. Lets change Rattata stats to a negative number to see what happens. 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. When I export sales data from my POS system, the date and time of the transaction are combined into one column. This allows you to perform calculations. 1. Method 1: Using SUMIFS Function. So, if you have criteria along rows and columns at a time, you can’t use the SUMIFS function alone for multiple matches and sums. 💥 Formula Breakdown. 3. =SUMIFS (sales,date,">"&DATE (2019,10,1),material,F13) Sum if date is greater than a specified date in Excel using the SUMIF and SUMIFS functions. 1. I tried with SUMIFS function but 27/09/16 is 42640 when converted to number while 27/09/2016 14:00:00 is 42640,5833333333 so it does not match. To do this, we just need a simple sum formula: But, since the total number of hours is greater than 24, by 7. . Using the ‘+’ operator – In an empty cell, enter the “=” sign, the first value (either enter the value or select the cell with the values), then press. So, let’s see the below steps to calculate the bonus using formulas in Excel: Step 1: In the Summary sheet, list all the sheet names you will sum. So I'm trying to create a sumifs equation that runs on a specific time/date range. 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. In the above screenshot, we can observe the sales of products X, Y, and Z. Sum_range should be the same size and shape as range. To use SUMIF in Excel, start by determining the criteria and selecting the range. To sum values within a certain date range, use a SUMIFS formula with start and end dates as criteria. This tutorial demonstrates how to use Excel SUMIF with an easy to follow example and takes you step-b. Click in cell B4. Looking at the image of Formula AutoComplete and the list of arguments, in our example sum_range is D2:D11, the. In the tab ‘Number’, select ‘Custom’. Then, choose the range from B2:B13 from the Salary Table. ) The example I’ve shown above is only one way to use these versatile functions. This will give you a sum of values, but this format is not correct and we need to. 2) In the Select Range section, specify the Lookup Value, Output Range as well as the Data table. Sum Time Applying the AutoSum Tool. The Sumif function in Excel has a very specific syntax that you need to use in order to get the calculation right. Click edit. The following criteria are entered on the worksheet, and referenced in the formulas: Start Date - cell D2. 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). The actual cells to add, if you want to add cells other than those specified in the range argument. Conditional Excel Sum based on dates range. Summary. Final result. g. Example 2: SUMIF formulas with text criteria. We don't need to enter a sum_range. The sheet 'Calls' is data from our phone system. 50. That's what goes in D4. criteria1: The first condition to retrieve the value from. In addition, the SUM function will work in time format. Get the SUM of Time Value Inserting Individual Cells. For instance, let's say you want to calculate the individual monthly hours worked for all your clients. It is typed =SUMIF: =SUMIF ( range, criteria,. . But both can be combined for common cause. The SUMIF function is designed to sum cells based on a single condition. 0. Applying “Not Blank” Operator. 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. please suggest or help me to solve this. The syntax of the SUMIFS function requires that you first specify the values to add up (sum_range), and then provide range/criteria pairs. Normally, when adding or summing times in Excel, you will get the finally calculated result within 24 hours as the left screenshot shown. Leave it set to General and leave destination set to F2. In the example shown the formula in cell F5 is: =SUMIFS(data[Amount],data[Location],"*, "&E5&" *") Where data is an Excel Table in the range B5:C16. You can apply the SUM function to add hours and Minutes in Excel. Note: The wildcard can be anywhere in the text string. In this example, the sum range is the named range "time", entered as an Excel time in hh:mm format. Download Practice Workbook. Remove any ticks from the Delimiters boxes and click Next. Additionally, you can use SUMIFS over SUMIF to add up numbers based on multiple. Here is an example: =SUMIF (A3:A10. Sum values based on criteria in another. The format will be in the Type list the next time you need it. Then try sum. 1. After that, refer to the range of cells where you have time values. i. criteria_range2: The second column, which. 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. Excel allows us to sum the values based on the week number by using SUMIFS and WEEKNUM functions. For example, user Marvin Duke has a total talk time. Since we’re adding the same range as the criteria range, we can omit the third argument (the. The function is available in Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower. SUMIF Function. Earlier we used =SUMIF (A2:A7, "Biscuits", B2:B7) to get the total cost of Biscuits. 1. INDIRECT () should take this into account. Fig: Sumif function in Excel. In Excel, the SUMIF and SUMIFS functions both sum the cells in a range that meet a certain condition, but they use slightly different behaviors:. SUMIF may not visibly work when you try to sum up times. 09-04-2014, 01:15 AM #12. What we want is to count AMOUNT of time. More criteria can be added, if needed, and this example shows totals in a date range for a specific region. Nov 26, 2020. With a helper column. We have demonstrated the different approaches to how you can make sure that the calc. 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. SUMIFS on the other. When counting hours and minutes, enter the info as hh:mm:00 and format it as hh:mm. After that, the Edit Name dialog box will pop up on the screen. In marketing, the use of Excel is pretty inevitable — but with these tricks, it doesn't have to be so daunting. 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. I have used “Pizza” since we only want to sum the cells with pizza sales. Select the column to sum. In the above screenshot, we can observe the sales of products X, Y, and Z. G4 : end time value given as cell reference. 1. The first criteria inside SUMIFS includes dates that are greater than or equal to week date in column F:How to create Power query or M language query for SUMIF function ? I have following formula available in Excel : Quantity =SUMIF( Concatenated , [@Concatenated], On time quantity ) where On time Quantity is depend on Quantity . 3. After installing Kutools for Excel, please do as this:. Overview Transcript. Excelでは時間の計算が足し算と引き算を使って簡単にできます。 SUM関数で時間を合計することもできます。 また、合計した結果が24時間を超えた場合は0時にリセットされて表示されることがあります。 「24:00」は「0:00」と表示されます。 When you are looking to sum numbers in one column when another column is equal to either A or B, the most obvious solution is to handle each condition individually, and then add the results together: SUMIF (range, criteria1, sum_range) + SUMIF (range, criteria2, sum_range) In the table below, suppose you want to add up sales for two different. In that case 9:00 + 11:00 would be displayed as 20:00. Stack Exchange Network. Now type our required format of cell – [h]: mm;@ and then click ok. First, we will see how to use LET to assign names to the SUMIF range, criterion, and sum_range parameters. 0. The SUMIF function enables you to tell Excel to add together the numbers in a particular range only when those numbers meet the criteria that you specify. You must remember how the date format work in Excel. Here’s an example: = sumproduct（（範囲- time（0 、30 、0 ））* （範囲> time（0 、30 、0 ））） 説明. By using the SUMIF function in combination with logical operators and cell references, you can easily sum values greater than 0 in your Excel spreadsheets. In order to sum the times over 30 minutes, we use the SUMPRODUCT and TIME functions. Click "OK" to apply the format to your cells. Nov 26, 2020. It's one of the most useful functions in Excel. You can use the Excel SUMIF Function to calculate the monthly sum of hours worked for client projects. . Function: =SUMIF(C2:C6,D2,B2:B6) Result: $900 Explanation: The range for the criteria is C2:C6, and the criteria is the value in D2, which is yes. I have a tip report that gives me tips by specific time. The SUMIF function is a built-in function in Excel. SUMIFS function. Click Kutools > Super LOOKUP > LOOKUP and Sum, see screenshot:. Count the number of transactions $100+ with this Excel COUNTIF formula. The blue links go to the written steps for each example. Follow the steps below to get the result –. 0. Let’s apply the SUMIF function in cell “F6″. . The following spreadsheet shows three examples of the Excel Sumif function used with text based critia. As the formula is copied down, it returns a count of each color listed in column F. SUM of SUMPRODUCTs -. 0. 2. The last row uses the sum function "=SUM(E9,E18)" to add the sub totals. Advanced IF functions. This is a function that adds values that meet multiple criteria. 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. For example, the simplest SUMIF formula =SUMIF (A2:A10,">10") adds the values in cells A2 to A10 that are larger than 10. Excel SUMIF between dates and times. So, in this example, select cell E4. WorksheetFunction. Sum_range should be the same size and shape as range. 1. excel; sumifs; sumproduct; Share. Along with. The first argument, range, is the range of cells to apply criteria to. B1:B19 = 10. The brackets ensure that Excel no longer suppresses the hours that go beyond a day. Step 1: Highlight the Cells Highlight all the cells you want to sum. The yellow marked boxes indicates the named ranges used. This guide to the SUMIF Excel function will show you how to use it, step-by-step. Use. If the sumif isn't adding properly, it's a problem in column F. Most of the time, the problem you will need to solve will be more complex than a simple. As long as the values in your date/time column are actual Excel date/time values, and not text, then the time values do not interfere with date calculations. Now, Excel SUMIFS cannot look up matching values for horizontal and vertical criteria simultaneously. In the Format Cells box, choose Custom in the Category list. Re: Need help; can't SUM hh:mm:ss. We used the ampersand ( &) operator to concatenate the “ greater than ” ( >) symbol with the value in cell D19. To add time entered as text use this formula array: = SUM ( TIMEVALUE (D$12:D$22) ) and format the cell as: [hh]:mm:ss. Step 3 - Setting the Parameters for the SUMIFS function. Another way is using the Excel SUMPRODUCT function where: Asterisk (*) is used as the AND operator.