# Sum and chart in excel

I have data like the following: Both Hour and Count are columns in an excel file.

``````Hour    Count
17  79
18  122
19  123
20  142
21  150
22  78
23  15
13  33
14  33
15  40
16  33
17  56
18  46
19  35
20  67
21  65
22  45
23  36
``````

What I want is to create a chart that shows over a period of 1 to 24 (hours) the total count. What's the easiest way to do this.

The chart should have a horizontal axis that runs from 1 to 24; and a vertical axis that goes from 0 on up.

In the case above the values should be combined like:

``````1 - 0
2 - 0
3 - 0
4 - 0
5 - 0
6 - 0
7 - 0
8 - 0
9 - 0
10 - 0
11 - 0
12 - 0
13 - 33
14 - 33
15 - 40
16 - 33
17 - 135
18 - 168
19 - 158
20 - 209
21 - 215
22 - 123
23 - 51
24 - 0
``````
-
Where is hour 1? – soandos Mar 23 '12 at 0:04
Not there. I'd like excel to chart a zero. – NotMe Mar 23 '12 at 0:41
You are going to have to be a lot clearer. Where is the data, what do you want on your axes, etc – soandos Mar 23 '12 at 1:14
Do you mean by total that the hour 17 figures should be totalled together? Can this be done beforehand by the data source? – Paul Mar 23 '12 at 2:07
@Paul: totalled together, can't be done before hand. – NotMe Mar 23 '12 at 4:16

Ok,

Assuming the hour is in column A and the count is in column B.

In column D, put the numbers 1-24

In column E, put the following formula:

``````=SUMIF(\$A\$1:\$A\$18,CONCATENATE("=",D1),\$B\$1:\$B\$18)
``````

This will product your second table above.

Then add a chart, and use \$E\$1:\$E\$24 as the data range, and \$D\$1:\$D\$24 for the categories. In the image below I have ordered the original data, but it isn't necessary.

-