Count values ...
 

Hi,

Column A contains a list of dates in the format dd/mm/yy. In column B
each date is allocated a value of either Y or N.

What I would like to do is calculate the number of times Y and N
appears for any given month.

Any ideas hwo to implement this please?

Many thanks,

Rob.

=SUMPRODUCT(--(MONTH(A1:A5)=4),--(B1:B5="y"))


"Rob Steed" wrote:

Hi,

Column A contains a list of dates in the format dd/mm/yy. In column B
each date is allocated a value of either Y or N.

What I would like to do is calculate the number of times Y and N
appears for any given month.

Any ideas hwo to implement this please?

Many thanks,

Rob.

Assuming your dates are in column A and your Y/N are in column B,
In C1 put this formula and copy down.....
=MONTH(A1)&B1

In D1 type the number of the month you wish to test.
In E1 type the Y or N you wish to test

In F1 put this formula........it will result the count of the conbination of
the MONTH in D1 and the Y/N in E1
=COUNTIF(C:C,D1&E1)

Change D1 and or E1 at will to check other conditions.

Vaya con Dios,
Chuck, CABGx3



"Duke Carey" wrote in message
...
=SUMPRODUCT(--(MONTH(A1:A5)=4),--(B1:B5="y"))


"Rob Steed" wrote:

Hi,

Column A contains a list of dates in the format dd/mm/yy. In column B
each date is allocated a value of either Y or N.

What I would like to do is calculate the number of times Y and N
appears for any given month.

Any ideas hwo to implement this please?

Many thanks,

Rob.

Thanks for the reply. I have this working but how can I distinguish
between years? The month function for Jan 05 would be the same for Jan
06 i.e 1.

Thanks,

Rob.

I don't know if you're talking to Duke or me, but if me,
then in C1 put =MONTH(A1)&"-"&YEAR(A1)&B1

and in D1 put 1-2005, or whatever month/year combo you want and format D1
for TEXT or use a leading apostrophe.

Vaya con Dios,
Chuck, CABGx3


"Rob Steed" wrote in message
oups.com...
Thanks for the reply. I have this working but how can I distinguish
between years? The month function for Jan 05 would be the same for Jan
06 i.e 1.

Thanks,

Rob.

Perhaps a pivot table would be far better for this, as it allows you to group
by monthly periods.

Select any cell in the table (make sure you have a column header for each),
then use DataPivot table. Click on the finish button and Excel creates a
Pivot table shell.

Drag the header for the Y/N values into the Row area and into the data area,
and the date header into the column area. Right click on the date header,
choose Group & Show DetailGroup, the choose month and choose year.




You'd end up

"Rob Steed" wrote:

Thanks for the reply. I have this working but how can I distinguish
between years? The month function for Jan 05 would be the same for Jan
06 i.e 1.

Thanks,

Rob.

If you were asking how to modify the formula I suggested to incorporate year,
it'd be

=SUMPRODUCT(--(MONTH(A1:A5)=4),--(YEAR(A1:A5)=2004),--(B1:B5="y"))



"Rob Steed" wrote:

Thanks for the reply. I have this working but how can I distinguish
between years? The month function for Jan 05 would be the same for Jan
06 i.e 1.

Thanks,

Rob.

perfect thanx.

Rob.