CODEWITHSUNDEEP
excelexcel-formula
Antoine Clement
Antoine Clement

Reputation: 43

Average of sum of column values

I have a table with 2 columns : dates (1st of the month) and values. Each date can appear an unknown number of time. I am trying to find a formula that would make a yearly average of the sum of the value for each month.

I can easily make several sumproducts to have the sum of values for each month and then average but I would prefer to limit the size of the formula.

Does anyone has an idea on how to do that, or if that's even possible?

Upvotes: 1

Views: 222

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37367

I assume that dates are in A column and values in B column.

The easiest way would be:

1) In third column (C), store the integers indicating the month. You will get this result by simple =MONTH() Excel function, ex.: =MONTH(A1), etc.

2) To get the average from particular month, say September (9th month), you need to enter the formula: 

= SUMIF(C:C,"=9",B:B)/COUNTIF(C:C,"=9")

If you want the average for different month, you just change the 9 in SUMIF and COUNTIF.

Upvotes: 2

jetblack
jetblack

Reputation: 155

You could make a pivot table, then drag the date in row field and values in value field. Then change the field setting of the values to 'average'.

Upvotes: 1

Related Questions