Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a spreadsheet where the columns contain values as follows:

Column(A)       B            C       D     E  

NameA        01/02/2010      5       4     5  
NameB        05/06/2011      4       3     2  
NameC        12/11/2010      3       5     3  
NameD        08/14/2012      4       4     4  


SUMMARY            2010      8       9     8  
                   2011      4       3     2  
                   2012      4       4     4  

and so on...

I want to calculate the total of column C where the Year in column B = 2010 (or 2011 or 2012, etc.) and create a summary table (possibly in another worksheet) accompanied by a chart.

I have looked at SUMIF, SUMIFS, and SUMPRODUCT.

I would like to know the best way to accomplish this task, preferably to capture the summary in a separate worksheet.

share|improve this question
Could you explain better what exactly you need to do? Because to me, quickly like that, it sounds like you only need to do a normal sum and then set the cell as a normal number. Also, a screenshot of the table would be great, because it's not exactly easy to visualise a table that's as text like this. – Ariane Jan 11 '13 at 19:31
I actually need to determine the average. I will put together a better question and resubmit with a screenshot of my data. – jjandrositz Jan 14 '13 at 19:15
Don't resubmit. Edit the question. Mods here hate duplicates. – Ariane Jan 15 '13 at 3:09

Definitely a Pivot Table is the way to go here. Sum column C by dragging it to the Values area and summarise by rows for column B by dragging that to the Rows area.

Then the trick to get where you need is to Group your rows - because they are dates, Excel will give you useful options such as by year, month, etc. Right click a row heading (ie a date), choose Group > select Year and unselect all others so you end up only with a summary by year.

Job done.

Add a chart using the PT as a data source and you should get a simple result.

Then go learn lots of other cool stuff you can do with PTs, such as add filtering, use slicers and stuff like that.

PS: consider making your source data into a Table if you want the PT to still work when you add more data later.

share|improve this answer

Let’s say that the years are in B97:B99.  Then set C97 to

=SUMIFS(C$1:C$4, $B$1:$B$4, ">="&DATE($B97,1,1), $B$1:$B$4, "<"&DATE($B97+1,1,1))

… adjusting the 1 and the 4 to reflect the location of your data.  This says add (sum) the values in column C for the rows where the value in column B is ≥ January 1 of the year indicated by the number in cell B97 but < January 1 of the subsequent year.  Then drag/fill down to row 99 and out to as many columns as you need.  Of course this can go into a separate worksheet; just use the Sheet1! notation.


If you wanted an average of these data, you could do

=SUMIF(args as above) / COUNTIF(2nd through 5th args)

If you want to handle the possibility that there are no data for a given year (so the COUNT would be zero, resulting in a division by zero error), do

=IFERROR(SUMIF/COUNTIF formula, from above, value to display if error)

share|improve this answer
I should have been more clear in defining the problem. – jjandrositz Jan 14 '13 at 19:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .