Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
=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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
perfect thanx.
Rob. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count how many different text values in an array. | Excel Worksheet Functions | |||
How do I do count calculations ignoring duplicate values | Excel Discussion (Misc queries) | |||
Count occurances of multiple values | Excel Worksheet Functions | |||
formula to count numbers of certain values | Excel Worksheet Functions | |||
Count number of Unique values | Excel Worksheet Functions |