Counting the actual used months in a range of Excel date values -

i want monthly average counts months containing transaction. easy summing transaction amounts , dividing number of months used.

i couldn't find native excel formula count months that, , trying sum on criteria based on row() function using table or named range didn't work. return 0 when attempting limit count year (or other criteria).

that formula looked this:

={sum(if(match(month(daterange),month(daterange),0)=(row(daterange)-min(row(daterange))+1),1,0))} 

that's array formula, way. looks see if row you're on same first row month referenced in sorted list. that's 1 problem every method i've tried; range needs sorted date.

the greater problem arises when attempting use and() or boolean expression limit named range 'daterange' specified year.

so question remains, how 1 specify particular criteria in single formula , count number of months in range based on criteria?

the solution came following non-array formula using sumproduct():

=sumproduct((month('transaction log'!$a$3:index('transaction log'!$a:$a, counta('transaction log'!$a:$a)+1))<>month(daterange))*(year(daterange)=2014)) 

the trick compare range of dates similar range, shifted 1 cell. comparing, instance, cells 1:5 against cells 2:6. specifically, you're looking places consecutive dates not in same month, , counting them.

unfortunately, method still predicated on having sorted range. way, can specify number of criteria limit scope of data. old method of estimating average costs per month involved simple ratio drastically over-represented monthly costs in cases.