average column based of month in separate column

**Freddobonanza** · 12-11-2011, 03:30 PM

excelpictureforhelp.jpg

Hello all,
As you can see in the picture, I want a formula that will average column B based on the month in Column A and place the answer in column F.
I hope this simple explanation is enough.
I have researched for the answer but have failed.
Thank you.

**TMShucks** · 12-11-2011, 03:35 PM

You should be able to use AVERAGEIF or AVERAGEIFS to get the result you want.

You would get a more definitive response, at least from me, if you posted a sample workbook rather than a picture of your worksheet.

Regards, TMS

**Freddobonanza** · 12-11-2011, 03:47 PM

Worksheetforaveragewithmonth.xlsx

Thank you for your quick response,

I have been looking at AVERAGEIF & AVERAGEIFS, but am struggling to conjure up a working function that will search for the month I want.

Attached is excel sheet.

Thank you

**TMShucks** · 12-11-2011, 04:04 PM

One way, try:

F2: =SUMPRODUCT(--(TEXT($A$2:$A$65,"mmmm")=$E2),--(B$2:B$65))/SUMPRODUCT(--(TEXT($A$2:$A$65,"mmmm")=$E2))

Copy down and across.

Regards, TMS

**Freddobonanza** · 12-11-2011, 04:12 PM

Thank you TMShucks,

Works a treat!!!

**TMShucks** · 12-11-2011, 04:16 PM

You're welcome. Thanks for the rep.

I note that you have different years in the data. Did you want to add all the values for each month regardless of year?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save