Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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.


Edit

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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