I have an excel spreadsheet that looks like this:
I would like to plot a 2D column chart that has the temperature (middle column) on the horizontal axis and their count (rightmost column) on the vertical axis. The problem is, the same temperature can appear multiple times in the table for different months and I would like the chart to add all the counts for a particular temperature no matter the month for which it is listed.
For example, the temperature 19C happens 9 times in month 7 and 4 times in month 6. My chart should have a count of 13 for that temperature.
Any help is appreciated.
2 Answers
Simply do not include the month in the graph data. Excel steps:
- Select data (B1:C14 as seen in your pic)
- Insert (tab), under Charts select 2-D Column
- Right-click chart, Select Data
- Uncheck Count. Edit (under Horizontal [right-side])
- Select 'Count' data (C2:C14 in your pic)
- OK twice past two prompts
In my opinion, the chart could not calculate data. I would like to suggest you handle this data first.
Please copy the columns of Temperature from this sheet (Sheet1) to another one (Sheet2).
Select cells of Temperature (B2: B14), go to Data tab > Remove Duplicates > OK.
- Add a new column (New Count), enter formula in B2: =SUM(IF(Sheet1!$B$2:$B$14=A2,Sheet1!$C$2:$C$14)), then pull down.
Then choose the cells of New Count to insert a 2D column chart.
Right click the chart > Select Data > Edit > Select the range (Sheet2!A2:A11) for the horizontal axis > OK > OK.
