Counting Dates from certain months within certain cells

[Paul H]

Hi all,

I am struggling to find away of counting cells that contain certain dates.

Example...

Cells below have these dates in date format
a1 = 05/07/201
a6 = 16/08/2011
a8 = 27/09/2011
a10 = 13/07/2011
a12 = 24/07/2011
a27 = 22/08/2011

In cell a30 I am trying to count the number of times a July date is found in the above cells. Answer should be 3

In cell a31 I am trying to count the number of times an August date is found in the above cells. Answer should 2

In cell a32 I am trying to count the number of times an September date is found in the above cells. Answer should 1

I have tried using countif and sumproduct and countifs but can't seem to make them work.

P.S. I can't use a range of cells i.e. A2:A27 as some of the cells within this range I don;t want to include in the count even though they have a July, August or September date.

Any help would be greatly appreciated

Thanks you in advance

Paul

 

[Robert Mika]

Excel Workbook
	A	B
1	05/07/2011
2
3
4
5
6	16/08/2011
7
8	27/09/2011
9
10	13/07/2011
11
12	24/07/2011
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27	22/08/2011
28
29
30	3	July
31	2	August
32	1	September
Sheet3

B30:B32 Custum format mmmm

 

[T. Valko]

Hi all,

I am struggling to find away of counting cells that contain certain dates.

Example...

Cells below have these dates in date format
a1 = 05/07/201
a6 = 16/08/2011
a8 = 27/09/2011
a10 = 13/07/2011
a12 = 24/07/2011
a27 = 22/08/2011

In cell a30 I am trying to count the number of times a July date is found in the above cells. Answer should be 3

In cell a31 I am trying to count the number of times an August date is found in the above cells. Answer should 2

In cell a32 I am trying to count the number of times an September date is found in the above cells. Answer should 1

I have tried using countif and sumproduct and countifs but can't seem to make them work.

P.S. I can't use a range of cells i.e. A2:A27 as some of the cells within this range I don;t want to include in the count even though they have a July, August or September date.

Any help would be greatly appreciated

Thanks you in advance

Paul

Try these...

A30:

=SUMPRODUCT(--(TEXT(N(INDIRECT({"A1","A6","A8","A10","A12","A27"})),"mmm")="Jul"))

A31:

=SUMPRODUCT(--(TEXT(N(INDIRECT({"A1","A6","A8","A10","A12","A27"})),"mmm")="Aug"))

A32:

=SUMPRODUCT(--(TEXT(N(INDIRECT({"A1","A6","A8","A10","A12","A27"})),"mmm")="Sep"))

 

[Paul H]

Thanks Robert your hard work and help, unfortunately I don't think this will work if I have read it correct. The reason being the cells are being referenced as a Range and not individual ones. It is quite important that I reference only certain cells. The reason is the example of cells I gave you are starts dates to training and the blank cells will be end dates with the same months but different days. Therefore if I ref the range the formula will bring back any end dates as well as the start which I need to show as separate totals.

If there is a way to ref certain cells with this formula then this would be great.

Thanks again

Paul

 

[Paul H]

T.Valko,

These work that is great!!!! Thank you for your help.

Also I did find another way of doing it using

=COUNTIFS(G2,">" & DATE(2011,8,31),G2,"<" & DATE(2011,10,1))+COUNTIFS(M2,">" & DATE(2011,8,31),M2,"<" & DATE(2011,10,1))

But the above would of meant me having a very long formula as I need to reference 34 different cells. So this will save a lot of time.

Thanks again!!!

I do have another question about extracting the actual dates from the same referencing cells an placing them into another cell altogether for each month, however I will send a new post for this so that it comes up on the search engines for other user to benefit!!

Thanks again

Paul

 

[Aladin Akyurek]

Hi all,

I am struggling to find away of counting cells that contain certain dates.

Example...

Cells below have these dates in date format
a1 = 05/07/201
a6 = 16/08/2011
a8 = 27/09/2011
a10 = 13/07/2011
a12 = 24/07/2011
a27 = 22/08/2011

In cell a30 I am trying to count the number of times a July date is found in the above cells. Answer should be 3

In cell a31 I am trying to count the number of times an August date is found in the above cells. Answer should 2

In cell a32 I am trying to count the number of times an September date is found in the above cells. Answer should 1

I have tried using countif and sumproduct and countifs but can't seem to make them work.

P.S. I can't use a range of cells i.e. A2:A27 as some of the cells within this range I don;t want to include in the count even though they have a July, August or September date.

Any help would be greatly appreciated

Thanks you in advance

Paul

Control+shift+enter, not just enter:

A30:

=SUM((TEXT(CHOOSE(ROW(INDIRECT("1:"&COUNT(A1,A6,A8,A10,A12,A27))),A1,A6,A8,A10,A12,A27),"mmm-yy")="Jul-11")+0)

A31:

=SUM((TEXT(CHOOSE(ROW(INDIRECT("1:"&COUNT(A1,A6,A8,A10,A12,A27))),A1,A6,A8,A10,A12,A27),"mmm-yy")="Aug-11")+0)

A32:

=SUM((TEXT(CHOOSE(ROW(INDIRECT("1:"&COUNT(A1,A6,A8,A10,A12,A27))),A1,A6,A8,A10,A12,A27),"mmm-yy")="Sep-11")+0)

 

[T. Valko]

I do have another question about extracting the actual dates from the same referencing cells an placing them into another cell altogether for each month, however I will send a new post for this so that it comes up on the search engines for other user to benefit!!



Paul

That's going to be tough to do.

You're going to need VBA in one form or another to accomplish that.

 

[Paul H]

Hmmmm!

That will be difficult as I don't have any knowledge of VBA.

I have just posted the thread so will try my luck and also see if there is another way I may be able to do it.

Thanks again

Paul

 

[Paul H]

Hi Biff,

Just one last question...

I have just popped your formula into my spreadsheet but there is a slight difference from my example as to how I am using it.

I am trying to reference cells on the same Row but in different columns.

So when I try to copy the formula down to the next row the cell references stay as the original Row.

Is there a way that the formula can be changed so it will auto change the Row number??

=SUMPRODUCT(--(TEXT(N(INDIRECT({"G3","M3","S3","Y3","AE3","AK3","AQ3","AW3","BC3","BI3","BO3","BU3","CA3","CG3","CM3","CS3","CY3","CY3","DE3","DK3","DQ3","DW3","EC3","EI3","EO3","EU3","FA3","FG3","FM3","FS3","FY3","GE3","GK3","GQ3","GW3"})),"mmm")="Jul"))

Cheers Paul

 

[T. Valko]

Hi Biff,

Just one last question...

I have just popped your formula into my spreadsheet but there is a slight difference from my example as to how I am using it.

I am trying to reference cells on the same Row but in different columns.

So when I try to copy the formula down to the next row the cell references stay as the original Row.

Is there a way that the formula can be changed so it will auto change the Row number??

=SUMPRODUCT(--(TEXT(N(INDIRECT({"G3","M3","S3","Y3","AE3","AK3","AQ3","AW3","BC3","BI3","BO3","BU3","CA3","CG3","CM3","CS3","CY3","CY3","DE3","DK3","DQ3","DW3","EC3","EI3","EO3","EU3","FA3","FG3","FM3","FS3","FY3","GE3","GK3","GQ3","GW3"})),"mmm")="Jul"))

Cheers Paul

Since the cell locations are at regular intervals we can use this much shorter formula:

=SUMPRODUCT(--(MOD(COLUMN(G3:GW3)-COLUMN(G3),6)=0),--(TEXT(G3:GW3,"mmm")="Jul"))

That's quite an improvement, eh?

Now you copy down as needed.