# Number Date Count Function

#### antrawson

##### New Member
Hiya, I tried to search but couldnt find the exact answer.

I have a list of entries in DD/MM/YYYY format. I want to create a lookup that will tell me for example in a certain cell range, how many entries were in October for example. I can then customise this to whatever month I want to know the entries are.

Further to this, each entry also has another value in it and I want to know how many in october are red for example.

Line content is:

DATE. TYPE
01/10/2021. RED.
01/10/2021. RED.
03/10/2021. BLUE.
05/10/2021. GREEN.

Any help appreciated thanks

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Fluff

##### MrExcel MVP, Moderator
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

#### Joe4

So, you are saying that "01/10/2021. RED." all appears in one cell?

If they are all structured in that format, here is one formula that I think should count up all entries in range A2:A10 that are for October 2021 for any version of Excel:
Excel Formula:
``=SUMPRODUCT(--(MID(A2:A10,4,7)="10/2021"))``

However, you should still update your account details, as Fluff suggested, as you may be able to take advantage of some new functionality and use other formulas too.
It would be helpful not only for this question, but future questions as well.

#### antrawson

##### New Member
So, you are saying that "01/10/2021. RED." all appears in one cell?

If they are all structured in that format, here is one formula that I think should count up all entries in range A2:A10 that are for October 2021 for any version of Excel:
Excel Formula:
``=SUMPRODUCT(--(MID(A2:A10,4,7)="10/2021"))``

However, you should still update your account details, as Fluff suggested, as you may be able to take advantage of some new functionality and use other formulas too.
Hiya
The data is in separate cells

#### Joe4

The data is in separate cells
OK. That was not quite clear.

Please update your account details as Fluff asked, because there are some newer formulas that make this easier to do, but we won't know if you are able to use them without knowing what version of Excel that you are using.

If you have one of the newer versions, you can use COUNTIFS like this:
Excel Formula:
``=COUNTIFS(A2:A10,">=" & DATE(2021,10,1),A2:A10,"<=" & DATE(2021,10,31),B2:B10,"RED")``

#### Joe4

Here is a formula that should work on the older versions (and pretty much any Excel versions):
Excel Formula:
``=SUMPRODUCT(--(A2:A10>=DATE(2021,10,1)),--(A2:A10<=DATE(2021,10,31)),--(B2:B10="RED"))``

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,139
Messages
5,835,624
Members
430,372
Latest member
contentment

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