How to Sum a time range in google sheets. Next, in the third argument of the SUMIF,. SUMIFS is an Excel function used to find conditional sums with multiple conditions. For example, user Marvin Duke has a total talk time. 0. Select the range title, and add filter by clicking. #9: Excel SUMIF to sum values with the time format. "After that, navigate to Data menu. With the knowledge and. I am Calculating Over Time of the employees in time format cell using Microsoft Excel 2007. To use Excel SUMIF with a date range, the criteria need to be constructed with logical operators. The column containing the criterion. In addition, the SUM function will work in time format. Here the SUMIFS function satisfies all the conditions mentioned. The square bracket syntax tells Excel not to "roll over" times greater than 24 hours. Select your desired format displaying hours, minutes, and seconds. SUMIF. The SUMIF function, also known as Excel conditional sum, is used to add up cell values based on a certain condition. First, in a cell enter “=SUMIF (“, for the range argument, refer to the product ID range that you have in table1. I am taking the sum of a range of cells where a SUMIF condition is met. Applying SUM Function to Autosum Time in Excel. Enter the following formula, and press Enter: =SUMIF (A$2:A2,”>=” & DATE (YEAR (A2),MONTH (A2)-11,DAY (A2)),B$2:B2) Copy the formula down to the last row with data. Syntax The generic syntax for SUMIF looks like this: = SUMIF ( range, criteria,[ sum_range]) The SUMIF function takes three arguments. The SUMIFS function has a few parameters. However, you say that =A1+A2+A3. Here is an example: =SUMIF (A3:A10. The difference is that the SUMIF function sums only those values that meet the criteria you specify. For a quick overview of 7 different ways to sum numbers with Excel functions, you can watch this 1-minute video. In other words, data that pass the logic test gets to be added up (or another column of your choice, that corresponds to the same. In Excel, you can sum up time first, and then format the result as you need. 3. 2. Otherwise, a formula of that form would return #VALUE. Right-click on the cell and select Format cells from the context menu. In the example below, SUMIF adds the values in Column B. In this tutorial, we will focus solely on the SUMIF function, the use of SUMIFS will be covered in the next article. 1. We use the SUMIF Excel function to identify and find the sum of cells with a specific criterion within a range. This guide to the SUMIF Excel function will show you how to use it, step-by-step. To sum values between a given start and end date, you can use the SUMIFS function. Although you only want to add the values that are greater than 10. I need to sum G (Sum time) if its less than 300 (<300), if B (Type) =1, if D (date) = input cell and if E( time) = a certain range that i can change, for example "7pm -12pm". The WorksheetFunction object can be used to call most of the Excel functions that are available within the Insert Function dialog box in Excel. The actual cells to add, if you want to add cells other than those specified in the range argument. By using SUMIF + SUMIF +…formula. On "Day 1" I enter the duration in column C for my first task which automatically calculates column D's answer (simply B+C=D). This will return the sum of the values within a desired range of cells that all meet one criterion. Steps: Select cell D5 and go to the Formulas tab >> Name Manager. I have used “Pizza” since we only want to sum the cells with pizza sales. SUMIFS allows you to sum values in a range that meets multiple conditions or criteria. In our formula, we take the initial time in cell A2, and then using the TIME Function we enter the hours from B2. Most of the time, the problem you will need to solve will be more complex than a simple. Add the range you want to use as criteria as the first argument of the SUMIF function. Excel Formula SUMIFS on date and time using date from given cell. The. Sum overlapping time. Fig: Time function 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. For instance, let's say you want to calculate the individual monthly hours. SUMIFS with Or. The range of cells that you want evaluated by criteria. We will apply the SUMIF formula in cell I7 to get Mexico’s total or gross sales. The SUMIF function is a built-in function in Excel. --1) Grand Total a range of cells. Let us follow these steps: Step 1. In the following dataset, we have. Skip to main content. From that box: Click on New. The steps are given below. The criteria argument is the criteria F2. B2 = -A1. Only one column must be selected. How to use SUMIF(S) in Excel with date criteria. In the Format Cells window, on the Number tab, choose "Time" as the Category. Review the following tutorial for a better understanding. . Here I have time in HH:MM:SS format. While working in Excel, we often need to sum values from a table based on a certain condition. Choose Custom in the Category list. Like the previous example, we will use the same dataset for this example too. The SUMIF allows only one condition to be evaluated, whereas the SUMIFS (introduced in Excel 2007) allows up to 29 criteria to be evaluated. The Name Manager dialog box will pop up. Another simple technique to calculate the duration between two times in Excel is using the TEXT function: Calculate hours between two times: =TEXT (B2-A2, "h") Return hours and minutes between 2 times:. The SUMIFS function was introduced in Excel 2007 and is available in all subsequent versions of Excel 2010, 2013, 2016, 2019, 2021, and Excel 365. With your blinking cursor. Enter the formula =SUMIF (C3:C191, "Ag", D3:D191). In the Type field, change the default value “hh: mm” to “[hh]: mm”. Now it's time to go over the plural version of SUMIF - Excel SUMIFS that allows summing values by multiple criteria. When counting minutes and seconds, enter the info as 00:mm:ss and format it as mm:ss. --2) Sum a simple range of cells. A1 = Date (1,1,1) ie 1hh:1mm:1ss. SUMIF formula to find sum for dates today or earlier. You could add values in a cell range only if they are greater than a certain amount. Consider a table having the sales data of companies A, B, and C for products X, Y, and Z. Consider a table having the sales data of companies A, B, and C for products X, Y, and Z. 2. Note: The wildcard can be anywhere in the text string. Excel: Calculate total for prior rows from last 24 hours. Format the cell. In Cell D2, enter: =SUM(C2,D1) This is to add up the value of the beginning credit limit and the header — Running Total. What we need to do now is to find the total number of hours that were worked. Select the cell where you want your result and enter the following formula, replacing the cell references with your own. SumIf (Range ("C2:C9"), 150, Range ("D2:D9")) End. To undo, select Thread Tools-> Mark thread as Unsolved. Ships in more than Ships in less thanAug 20, 2012. The yellow marked boxes indicates the named ranges used. 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. 50, which is the total amount associated with financial reporting audits. HOUR, MINUTE, SECOND. Things to Remember About Sum Time in Excel As we discussed, these time calculations mostly depend on the format you choose for your result cell; if you are. 2. However, sometimes you only want to add up the cells that meet certain criteria. After you type =SUMIFS (, Formula AutoComplete appears beneath the formula, with the list of arguments in their proper order. Just below the word Title you will see a dropdown with the word No prefix. For example, the simplest SUMIF formula =SUMIF (A2:A10,">10") adds the values in cells A2 to A10 that are larger than 10. 4. Now follow the steps below to see how to incorporate them together, to sum up, the colored cells. 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. It allows you to Sum the values in a range that meet a criteria that you specify. Like this: =SUM. 0. Step 4: In the “data validation” dialog box, select “list” under “allow” criteria. 1. Learn how to use the Lookup, Text, Logical, Math, Date & Time, Array plus more functions & formulas. 0423726851851852 after clearing the #s number format. I'm trying to find an average score based on certain criteria. All you need to do is ensure that your data is in the correct time format. Doing a conditional sum in Excel is a piece of cake as long as all the values to be totaled are in one column. Subtract Your Times. 1. As long as the date format is valid, Excel will be able to use it to calculate the sum. Looking at the image of Formula AutoComplete and the list of arguments, in our example sum_range is D2:D11, the. Date_Time Total. As a financial analyst, SUMIF is a frequently used function. In marketing, the use of Excel is pretty inevitable — but with these tricks, it doesn't have to be so daunting. g. [1] Select the cell where you'd like the answer to populate. Total Sales Measure. Solution 4: Use the VALUE Function. 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. Google Sheets has two functions to add up numbers based on conditions: SUMIF and SUMIFS. WorksheetFunction. First, select cell E6. 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. Step 4: Click Enter. ) The example I’ve shown above is only one way to use these versatile functions. The formula is explained in the next section. Use. =TEXT(SUM(C5:G5),"[h]:mm") Now, press the ENTER key. 1. It will sum up cells that meet the given criteria. SUM will ignore text and is why it will return 0. As I said, we need to use a tilde with an asterisk to get the sum of values. Release all keys. sum_range Optional. Example 7: Using SUMIF with Date Range (Month and Year) Criteria. Additionally, you can use SUMIFS over SUMIF to add up numbers based on multiple. 2. On "Day 2" I have three tasks to complete: Task 1: 09:00 - 10:00. ⏩ In cell F4, type the following formula. 2. Getting SUMIFS to work in array with. How to sum if between two dates in Excel. The sheet has daily hours calculated for a two week period, with subtotals for each week. Here, B5:B13 is the range where the condition is applied. The SUMIF function returns the sum of values for a specific criterion. Using SUMIF for Non-Blank Cells. 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. SUMIF and SUMIFS functions can be used to sum values that meet a criteria. 1. Mouse right click and go to Format Cells. Although a pivot table is my preference, if you really just want a formula, then this requires SUMIFS not SUMIF. --2) Sum a simple range of cells. When I add the cells using + it works perfectly but when using the SUM function it always gives 0. 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. Select([On time quantity], each [Concatenated] = [@Concatenated])) Message 2 of 3 713 Views 0 Reply. Step 2: Now, select the range B7:B24 and put a comma to separate it. In my result, its calculated as hh and mm. _________________. If it isn't, performance may. 1. #1. Excel VBA SUMIF Super slow code. 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. 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. You could add values in a cell range only if they are greater than a certain amount. 2. The old SUMIF and COUNTIF have been around since Excel 97. After 2007, the SUMIFS function was created which allows a. In formula I am using index to match the names and to return the sum of all that is assined to Item1, then Item2 and Item 3. So if I try to do a sumifs where the hour is >=22:00 and <23:00, the formula works. Steps: Firstly, you have to select a different cell E5 where you want to see the result. Click to read our step by step guide on sumif excel. The SUMIF function below (two arguments) sums values in the range A1:A5 that are less than or equal to 10. The function is available in Excel 365, Excel. Extract Data Quickly From Tables with Excel's SUMIF Function. Note that SUMIFS is not case-sensitive. As you can see the formula returns 0:25. Or you can use this way: How to mark a thread Solved. Excel SUMIF variable date range. 09-04-2014, 01:15 AM #12. As shown in the example, you can also add a helper column to calculate and sum time deltas. The SUM function updated the total sum from D12(3092) to D12(3189). To sum data by group you can use the SUMIF function or the SUMIFS function. The last row uses the sum function "=SUM(E9,E18)" to add the sub totals. Subtract Your Times. The SUMIF formula returns the sum of cells based on one criterion (a result that matches one condition). Calculate Hours with a Simple Formula. The function sums only those cells in the range from C2 to C16 to which this condition applies. Use the Formula: = COUNTIFS ( range , ">=" & F4 , range , "<=" & G4 ) range : named range used for birth date values D3:D11. When I manually sum values in column K in the data itself, the answer is correct. Learn. Yes visibly. Advanced IF functions. 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. When using sumifs, I'm able to sum the total without the second criteria ra. However, the date & time are combined into 1 column (ex. If the criteria is in the range itself, follow these steps: Type =SUMIF ( in a new cell. First, in cell C2, enter the sum function. Similar to the SUMIF function, SUMIFS has one sum range. Then, the string “Online” refers to the criteria argument to apply within the given range. Make sure the selected range (s) contains numerical data. 1. ) The conditions are referred to as criteria1, criteria2, and so on, which can check things like: The criteria_range1, criteria. Re: Need help; can't SUM hh:mm:ss. 1. To get the color code, we will use the GET. Every day since 1900/01/00 (arbitrary chosen date) are indexed. Excelでは時間の計算が足し算と引き算を使って簡単にできます。 SUM関数で時間を合計することもできます。 また、合計した結果が24時間を超えた場合は0時にリセットされて表示されることがあります。 「24:00」は「0:00」と表示されます。The SUMIF Function: an Overview. First, select a cell where we want the sum of ‘X’ sales results. Total Sales = SUM ( Sales [Sales Amount] ) I want to return Total Sales where the Brand = Adventure Works. Ability to use criteria with logical operators, like less than (<), allows adding values between other values. Writing a Sum Formula. I'm looking for a formula to match with the first 5 numbers that is the day, regardless the time. In the example shown, the formula in cell F5 is: =SUMIFS (C5:C16,B5:B16,"*hoodie*") This formula sums the quantity in column C when the text in column B contains "hoodie". We can use both Excel SUMIF and SUMIFS functions to sum values based on date criteria. Creating a running total by using the SUM function is pretty similar to using simple addition. Figure 6. Next, Insert the following formula: How to sum time in Excel. It gives a single output and. 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. in the image i just had =sum(D8:D20) What did i do wrong in my formula that it inst working?. Report abuse. The “<>” symbol is used to specify the values that are not blank. Right Click on your total Click on Format Cells On the Number Tab, Select "Number" and 2 Decimal Places In your formula bar, add *24 to the end of the sum formula (example =SUM (A20:G20)*24 ) difference between Method A and Method B is. The SUMIF function adds only the values that meet a single criteria. I need to know what is the sum value between 00:00 and 01:00 (which is 8). After that, the steps are the same for both methods; click Custom in the category. Step 3: In your Total cell enter the Excel formula " =SUM ( " and then select the cells with the hours in it. Is this possible? At the moment the user uses text to columns to split out the time into the next column, but I'd rather eliminate this step if possible. read more. In the 1st step, simply mark Delimited. This can be achieved by the help of the formula – TODAY (). 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. 4/21/2014 13:30). #1. The criteria can be within the range of values itself, or in a different range that is the same size as the values range. First, select a cell where we want the sum of ‘X’ sales results. Now, mark the General in a last step >> press Finish option. Download Practice Workbook. What formula can I use. The following SUMIF function gives the exact same result. 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. Formula Syntax: =SUMIF(range, criteria, [sum_range]) Arguments: range-Range of cells where the criteria lies. Press Ctrl and the minus button at the same time ( Ctrl - ), which is the Excel shortcut for Delete. Fig: Year function in Excel. SUM (if) function for multiple criteria + date/time that is dynamic. Step 1: Select an empty cell. Example: Apply SUMIFS Function to Get Summation Based on Multiple Criteria in Excel. Using the SUMIF function syntax, you will first need to provide which values you need to sum up and then proceed to give the range dates. 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. Sum the entire column and then subtract the cells you don't want to include in the total (cells B1 to B3 in this example): =SUM (B:B)-SUM (B1:B3) Remembering the worksheet size limits, you can specify the upper bound of your Excel SUM formula based on the maximum number of rows in your Excel version. Given the above, the formula would then become:The sum function allows you to add all your selected values from your data. --1) Grand Total a range of cells. The first thing you need to know is the syntax for using SUMIF in a formula. Normally, when adding or summing times in Excel, you will get the finally calculated result within 24 hours as the left screenshot shown. 1. We don't need to enter a sum_range. This will give you a sum of values, but this format is not correct and we need to. Post Reply Helpful resources. Horizontal SUMIFS with two vertical criteria. please suggest or help me to solve this. SUMIFS function. It updates the SUM when values inside of the range are changed. In the cell you calculate the SUM in, format it as [mm]:ss to count AMOUNT of time rather than TIME itself. Formula in H130: =COUNTIF (H125:H128,"<20")+COUNTIF (H125:H128,">30") Here if use the COUNTIFS, will. This formula gets that data. 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. The SUMIF function is designed to sum cells based on a single condition. --3) Running Total. Microsoft MVP 2010 - Excel. How to include cells with today date in. SUMIF function is, in a way, a combination of SUM and IF statements. Let’s take a look at the function’s syntax, and then I’ll show a quick example. Pivot Tables. SUMIFS function. I need to sum the tips for each hour of the day starting at 5AM. What we want is to count AMOUNT of time. Just remove your TEXT () function in COLUMN H formula. 🔗 Steps: First of all, go to Formulas Defined Names Name Manager. Now follow the steps below to see how to incorporate them together, to sum up, the colored cells. In the first example we're using =((B2-A2)+(D2-C2))*24 to get the sum of. please suggest or help me to solve this. 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. =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. You can do something like this. The following spreadsheet shows three examples of the Excel Sumif function used with text based critia. I then just want to sum all the volume that is between the times. Then, enter the SUMIF formula, specifying the range, criteria, and sum range. SUMIFS Between Two Dates and Times in Excel. 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). By default when you SUM time in Excel, it counts time of day + time of day. To sum values less than a given value, you can use the SUMIF function or the SUMIFS function. the date contained in C3) and used the formula: =SUMIF (B2:B11,"<="&C3,A2:A11) However in this instance I need each date in B to be less than or equal to the date in C. How to Sum Time in Excel - Microsoft Excel Video. Use SUMIF by Date in Excel. In the Type box, at the top of the list of formats, type [h]:mm;@ and then select OK. 2) In the Select Range section, specify the Lookup Value, Output Range as well as the Data table. Report a problem with this page. To use Excel SUMIF with a date range, the criteria need to be constructed with logical operators. Use the “hh:mm” or “h:mm AM/PM” format to enter time values. But how to set the correct time format? Well, it’s easy. Lets change Rattata stats to a negative number to see what happens. Combine Other Excel Functions with SUMIFS to Match Multiple Horizontal and Vertical Criteria. The Name Manager dialog box will pop up. I need to sum all the individual sales on a given date, but because of the addition of the time stamp, my SUMIF formula won't work. Along with. The SUMIF function allows you to sum values when they meet a criteria. ]) Where: Sum_range (required) - the range to be summed. For example, if you use a formula like SUBTOTAL (109, A1:E1) to sum numbers in a. Next, for the criteria, we need to give which employee ID to be considered. 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). Looking at the image of Formula AutoComplete and the list of arguments, in our example sum_range is D2:D11, the. Plus symbol (+) is used as the OR operator. The Sumif function in Excel has a very specific syntax that you need to use in order to get the calculation right. 10. Help us improve. The SUMIF function is used widely amongst spreadsheet users as it is a simple Excel function. 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. Now, go to the Editing group in the Home tab and click on the Autosum Option icon (Shown in the figure below) Consequently, you will get the Sum of times for each row. 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. That's where the SUMIF function comes in handy, along with the more capable SUMIFS function. The second. condition statements, so I use it as a replacement for the SUMIF function, and I no longer use SUMIF. 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. The sheet has daily hours calculated for a two week period, with subtotals for each week. In the formula, we used the SUMIFS function to sum values from individual sheets and then added the sum values from different sheets with the AND (+) operator. I have dates in column B. Here the arguments to the function is given as cell references and named ranges. For example, the A1:A20 range. Click Kutools > Super LOOKUP > LOOKUP and Sum, see screenshot:. SUM_range is the cell range used to evaluate based on specified criteria. A few methods to add the Add Time in Excel, namely, Using the SUM function – In an empty cell, enter the SUM formula, and select the time values cells or cell range to get the total. i also have time and it doesnt take the time into consideration. 18. I have previously had to complete this task with reference to a single date (e. In that case, we can simply add two or more SUMIFS functions for multiple criteria. For instance, 9:00 AM + 11:00 AM would be displayed as 8:00 PM. 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. To sum values within a certain date range, use a SUMIFS formula with start and end dates as criteria. criteria – The criteria indicating when to sum. To sum numbers if cells contain text in another cell, you can use the SUMIFS function or the SUMIF function with a wildcard. Right-click and pick "Format Cells," or go to Home > Format > Format Cells. Click in cell B4. . Insert the criteria to be used in the SUMIF formula. How to sumifs based on date and time in excel? 1. Select the cell into which you want your sum to appear. There are three arguments: =SUMIF(range, criteria, sum_range). For whatever reason, if the SUMIF function is not working properly, you can use the alternative formula SUMPRODUCT. Learn more at the Excel Help Center: staffing from column B for those flights is 3, 2, and 4, so the total staffing requirement at the time of the 3:30 flight is 9. This time our criteria is a text i. Key Takeaways. Click Finish. In this example the goal is to sum the numbers in the range F5:F16 when cells in the range C5:C15 contain "Red". But how to set the correct time format? Well, it’s easy. Figure 1. Share. One option is to convert the text into a true time, but since the time is in a format that Excel can recognize as a time we can do it in the formula: =SUMPRODUCT (--C2:C11) The -- will force the text into a number and the SUMPRODUCT will iterate the array and sum the results. I'm trying to SUMIF data based on two criteria: The title of the column (horizontal) In between two dates (vertical) I have tried a number of functions to solve this problem but to no avail. If a name is equal to the name in F4, then sum the corresponding cell from the range starting in D2. SUMIF formula to find sum for dates today or earlier. Excel SUMIF Function Condition with Numerous Comparison Operators & Cell Reference. 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:. 0. Use the steps above to open the function wizard and then add the following search criteria to the wizard: • Add Sum_range of E2:E17 to sum the value of the sale. To sum values when corresponding cells are not blank, you can use the SUMIFS function. In this step-by-step tutorial, learn how to use the SUMIF Function in Microsoft Excel. the final Output is got by summing all of them => 2950. Excel's SUMIF function is similar to SUM we've just discussed in the way that it also sums values. To calculate the sum based on criteria, start by selecting a cell where the result will be displayed. 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.