Find sum of totals made on a given day

Question

I have this simple table of data in Excel and I need a formula to find the total amount for today. So it would be a sum of all amounts that fall between 00:00 and 23:59 for the date on TODAY(). How can I do this?

To (possibly) complicate things slightly, I can't add any more columns to the source spreadsheet (the picture below). All the math is done in a separate sheet. So I can't add another column to each row to add some helper numbers/interim steps. Ideally it would all be one big formula I can plug into one cell on the separate sheet.

Example data:

Answer 1

First thing to figure out is if your dates are text or if they are numbers formatted by excel to display as dates. Its a very important distinction and you want the later as it allows you to use more of excels built in formulas. Some possible tests are

• =ISTEXT(A2)
• =ISNUMBER(A2)
• Change the cell formatting to general and see if the displayed contents change

For the sake of this solution, your dates will be assumed to be numbers. If they are not, there are plenty of questions/answers on how to convert text date to numbers/excel format.

UNDERSTANDING TIME/DATE IN EXCEL

Excel stores the date as an integer. It is the count of the number of days since January 0, 1900. So 1 is January 1st 1900. (I believe on make the reference year is 1905 but I could be wrong). So all dates in excel are simply integers with some special formatting applied to display them in some manner we are used to seeing. Time on the other had is stored a % of a day or if you prefer decimal of a day. 0.5 represent half a day or lunch. So if you need to just find the date from a mixed time date, you need to strip off just the integer portion. conversely if you just need the time, you just need to strip of the decimal portion.

Option 1 SUMPRODUCT

SUMPRODUCT is an regular function that performs array like calculations. As such you want to limit range reference to roughly your data and not use full column/row references. If you wind up doing the later you can bog down your machine with excessive useless calculations on empty cells.

=SUMPRODUCT((INT($A$2:$A$12)=TODAY())*$B$2:$B$12)

Note TODAY() is a volatile function. This means that it recalculates every time something on sheet changes even if it does not affect the function. A non-volatile function (most regular functions) only recalculate when a change that affects them occurs. As a resul,t if you copy a volatile function to a lot of cells you can wind up bogging down you computer every time something on a sheet is changed/entered.

Option 2 - SUMIFS

SUMIFS is a regular formula that you can use full column references in without worry and my preferred option on how to deal with this situation. This formula takes the form of:

SUMIFS(Range to be summed, range for condition 1, Condition 1 check, ..., range for condition n, Condition n check)

so in your case you want to check the start of the day and end of the day so your formula will have to condition checks and should wind up looking something like the following.

=SUMIFS($B$2:$B$12,$A$2:$A$12,">="&TODAY(),$A$2:$A$12,"<"&TODAY()+1)