# Count the number of cells between a range of specified dates

#### ianmn

##### New Member
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

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Re: Count the number of cells between a range of specified d

ianmn said:
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.

Replies
0
Views
371
Replies
3
Views
433
Replies
6
Views
522
Replies
7
Views
665
Replies
12
Views
569

1,203,514
Messages
6,055,842
Members
444,828
Latest member
StaffordStag

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back