2

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?

2

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.

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

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.

| improve this answer | |
0

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

| improve this answer | |
  • 3
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.