# Summing totals from the same 'hour'

I have an excel document representing the time and value of sales throughout a given day, with the intention to chart them hourly.

I currently have a simple table that looks somewhat like this, where I have rounded each time to it's nearest hour:

``````+--------------+
| Time | Sales |
+------+-------+
|  7   |   2   |
+------+-------+
|  7   |   3   |
+------+-------+
|  8   |   2   |
+------+-------+
|  8   |   6   |
+------+-------+
|  8   |   7   |
+------+-------+
|  9   |   9   |
+------+-------+
|  9   |   3   |
+------+-------+
|  9   |   4   |
+------+-------+
``````

This goes on for another 800ish cells. What will be the easiest way to sum the sales for each hour so it's in an easily graphable format?

Well, you could use another worksheet that contains a column of hours, and next to each hour you would use a formula with the `SUMIF()` function.

Assuming the hours in your above table are in column A with the sales in column B, the formula would look something like this:

In this illustration, the data that contains your above table is in the sheet called `RawData`.

``````=SUMIF(RawData!A:A,A1,RawData!B:B)
``````

``````+----------------------------------------------+
| Hour |        Sum                            |
+------+---------------------------------------+
|  7   | =SUMIF(RawData!A:A, A1, RawData!B:B)  |
+------+---------------------------------------+
|  8   | =SUMIF(RawData!A:A, A2, RawData!B:B)  |
+------+---------------------------------------+
|  9   | =SUMIF(RawData!A:A, A3, RawData!B:B)  |
+------+---------------------------------------+
``````

Which would show the output as:

``````+--------------+
| Hour |  Sum  |
+------+-------+
|  7   |   5   |
+------+-------+
|  8   |  15   |
+------+-------+
|  9   |  16   |
+------+-------+
``````

Breaking Down the Formula
The formula has three parts:

Range is the range of cells you want evaluated.

In this case, it will be Your first column you provided in your question, which I assumed to be Column A (on our theoretical sheet `RawData`).

Criteria is the condition or criteria in the form of a number, expression, or text that defines which cells will be added

In this case, it's the hours within the Range we provided earlier. On our separate sheet we created, we typed out 7, 8, 9 as you see in my example. We will reference those cells to be our Criteria.

Sum_range are the actual cells to sum.

In your case this will be Your column B that you provided in your question on the sheet `RawData`. These are the numbers that are actually added.

• Worked perfectly, separate worksheet not necessary except for organisation. – nuggetbram Feb 2 '18 at 3:16

You can also use SUMPRODUCT function to get the total of Hourly Sales.

``````=SUMPRODUCT((\$A\$2:\$A\$50=\$A2)*(\$B\$2:\$B\$50))
``````

NB: where A2 is the First Cell have 7. Drag the Formula Down till is required, Excel will automatically change the Row number of Cell Address A2.

Short answer as this has already been closed... you could use a PivotChart.

• Tim, the question hasn't been closed. The OP just picked an answer they liked (probably the first and only answer at the time). The rest of the world may prefer a different one. I agree that a pivot table is the logical, simplest approach. But this post is really just a comment, hinting at a direction to investigate rather than providing an actionable solution. As such, it might even attract downvotes. Can you expand this with the "how to"? – fixer1234 Feb 6 '18 at 22:09