Need your help averaging based on month & other criteria

Hi everyone, I have a workbook with one data sheet and I need to average data based on the month and several other criteria. My columns are set up in the following order: Business Unit (A), Product Area (B), Product Desk (C), Account (D), Date (E) and Values (F). Based on the last reporting month I would like to calculate an average month to date value, e.g. YTD November (Jan-Nov divided by 11).

I have tried it with SUMIFS(F2:F25,E2:E25,A2:A25,Helper cell for YTD November,D2:D25,Link to other sheet with Account Description) / Helper cell for YTD number

Is there a way to solve this in a smarter way, perhaps with an array formula (Sumproduct, Average(if, etc.)?
I have attached file with the raw data and would appreciate any help I could get.

Thanks!

With proper Excel dates in Column E

in F14

=SUMIF($E$2:$E$13,"<=" &EOMONTH(E14,0),$F$2:$F$13)/MONTH(E14)

Copy down

John, thanks for your prompt response. This works beautifully, but, in my original data sheet i have several Business units. Is there a way to add the Business Unit as another criteria and use SUMIFS? If so, how would that work in combination with the EOMONTH formula?

Thank you

Try

=SUMIFS($F:$F,$E:$E,"<=" &EOMONTH(E14,0),$A:$A,business)/MONTH(E14)

Replace "business" with a cell or TEXT e.g "Business 1"

NOTE: with SUMIFS, data to be SUMMED is first range whereas with SUMIF it is the last .

It works perfectly with the sample data, however I get a #VALUE error in my live data file. I made sure that I have my dates custome formatted 'mmm'. Strange...

I should say that my live data file is formatted as a table. Does this make any difference?

John, I have included the data table, excluding any sensitive data. Sheet1 contains the formula and some explanation where custom formatting has been applied. Can you spot any error?

Thanks v much!

Apologies, forgot to include the data file. Unfortunately due to data upload restrictions i wasn't able to upload the whole full data file.

Now file attached!!!

Does not work because the dates are TEXT not Excel dates

With proper Excel dates in Column E

as per #2

Check the sample file I supplied in #2: ALL dates are entered as 01/10/2016 etc and formatted as "mmmm".

ALWAYS enter dates in dd/mm/yyyy format.

Thanks for your help with this! Fully understood now. http://www.excelforum.com/images/smilies/rolleyes.gif

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.