Count the number of cells between a range of specified dates

[ianmn]

Can anyone help?
I,m working a Invoice spreadsheet with coloumns;
From A-E 17 to A-E 1006
A= Date B= Age C= Invoice Number D= Customer Name E= Total sale

I have a formula for entering a date range to give the value of sales between the dates, but I would like to know how to get the number & average of the sales between these date. (A9 = start date b9 = end date)
I have tried with count & average formula,s with no luck. Any help would be much appreciated.

Thank you
Ian

 

[Aladin Akyurek]

Re: Count the number of cells between a range of specified d

Can anyone help?
I,m working a Invoice spreadsheet with coloumns;
From A-E 17 to A-E 1006
A= Date B= Age C= Invoice Number D= Customer Name E= Total sale

I have a formula for entering a date range to give the value of sales between the dates, but I would like to know how to get the number & average of the sales between these date. (A9 = start date b9 = end date)
I have tried with count & average formula,s with no luck. Any help would be much appreciated.

Thank you
Ian

One option is to build a pivot table from your data. Otherwise

Count...

If there is a sales figure for every date in the date range:

=COUNTIF(DateRange,">="&A9)-COUNTIF(DateRange,">"&B9)

Otherwise:

=SUMPRODUCT(--(DateRange>=A9),--(DateRange<=B9),--ISNUMBER(SalesRange))

Average...

=AVERAGE(IF((DateRange>=A9)*(DateRange<=B9)*ISNUMBER(SalesRange),SalesRange)

which you need to confirm with control+shift+enter instead of just with enter.