Number Date Count Function

antrawson

New Member
Joined
Jan 4, 2012
Messages
45
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,095
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 4, 2012
Messages
45
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
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"))
 
Master Excel Bundle

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.

Forum statistics

Threads
1,152,118
Messages
5,768,219
Members
425,460
Latest member
Astros1243

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top