# Calculating a sum on an integer column when another column includes the year criteria for the calculation

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.

-
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

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.

-

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`)`

-
I should have been more clear in defining the problem. – jjandrositz Jan 14 '13 at 19:13