Countif

ttratl

Board Regular
Joined
Dec 21, 2004
Messages
168
Hi All,

Is there an easy way of counting the number of instances certain dates appear in a range?

I am using this formula:
{=(COUNTIF(LABS!$A$5:$A$21,">=1/5/2006")-COUNTIF(LABS!$A$5:$A$21,">=1/6/2006"))}
to count the number of times any date in May 2006 appears in the range.

It seems overly complicated!

Any suggestions?
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,497
Office Version
  1. 365
Platform
  1. Windows
ttratl

Here is another suggestion, but it is not much (if any) simpler than your solution:
=SUMPRODUCT(--(MONTH(LABS!$A$5:$A$21)=5),--(YEAR(LABS!$A$5:$A$21)=2006))
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
ttratl said:
Hi All,

Is there an easy way of counting the number of instances certain dates appear in a range?

I am using this formula:
{=(COUNTIF(LABS!$A$5:$A$21,">=1/5/2006")-COUNTIF(LABS!$A$5:$A$21,">=1/6/2006"))}
to count the number of times any date in May 2006 appears in the range.

It seems overly complicated!

Any suggestions?

Not sure what you mean by overly complicated, but entered with just Enter

=SUMPRODUCT(($A$5:$A$21-DAY($A$5:$A$21)+1=$B1)+0)

Where B1 houses the first day of the month you're trying to count.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,377
Members
412,589
Latest member
ArtBOM
Top