Number Date Count Function

antrawson

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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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’)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
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"))
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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
Back
Top