Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Compare if same month in cell vs columns
On sheet 1 I have the months across the top row and lets say that the month
of March is in c1. On sheet 2 I have a column (d1:d20) with many dates, and I want to count the number of dates in the month of March. I think it would go something like this, =countif(month(sheet 2(d1:d20)),sheet 1(c1)) I may not have all of the () in the right place but hope you get the idea. Thansk |
#2
|
|||
|
|||
Compare if same month in cell vs columns
Assume Sheet1 column headers are plain text, while dates in Sheet2 are true
dates, try this *array* formula: =COUNT(IF(TEXT(Sheet2!D1:D20,"mmmm")=C1,Sheet2!D1: D20)) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "MHY" wrote in message ... On sheet 1 I have the months across the top row and lets say that the month of March is in c1. On sheet 2 I have a column (d1:d20) with many dates, and I want to count the number of dates in the month of March. I think it would go something like this, =countif(month(sheet 2(d1:d20)),sheet 1(c1)) I may not have all of the () in the right place but hope you get the idea. Thansk |
#3
|
|||
|
|||
Compare if same month in cell vs columns
Sorry, I should have stated that both can be in the same format. mmm-yy,
mm-dd-yr. Thanks "RagDyer" wrote: Assume Sheet1 column headers are plain text, while dates in Sheet2 are true dates, try this *array* formula: =COUNT(IF(TEXT(Sheet2!D1:D20,"mmmm")=C1,Sheet2!D1: D20)) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "MHY" wrote in message ... On sheet 1 I have the months across the top row and lets say that the month of March is in c1. On sheet 2 I have a column (d1:d20) with many dates, and I want to count the number of dates in the month of March. I think it would go something like this, =countif(month(sheet 2(d1:d20)),sheet 1(c1)) I may not have all of the () in the right place but hope you get the idea. Thansk |
#4
|
|||
|
|||
Compare if same month in cell vs columns
Then try this *array* formula:
=COUNT(IF(MONTH(Sheet2!D1:D20)=MONTH(C1),Sheet2!D1 :D20)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "MHY" wrote in message ... Sorry, I should have stated that both can be in the same format. mmm-yy, mm-dd-yr. Thanks "RagDyer" wrote: Assume Sheet1 column headers are plain text, while dates in Sheet2 are true dates, try this *array* formula: =COUNT(IF(TEXT(Sheet2!D1:D20,"mmmm")=C1,Sheet2!D1: D20)) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "MHY" wrote in message ... On sheet 1 I have the months across the top row and lets say that the month of March is in c1. On sheet 2 I have a column (d1:d20) with many dates, and I want to count the number of dates in the month of March. I think it would go something like this, =countif(month(sheet 2(d1:d20)),sheet 1(c1)) I may not have all of the () in the right place but hope you get the idea. Thansk |
#5
|
|||
|
|||
Compare if same month in cell vs columns
Is there anyway I can send you an example is spread sheet form?
"MHY" wrote: On sheet 1 I have the months across the top row and lets say that the month of March is in c1. On sheet 2 I have a column (d1:d20) with many dates, and I want to count the number of dates in the month of March. I think it would go something like this, =countif(month(sheet 2(d1:d20)),sheet 1(c1)) I may not have all of the () in the right place but hope you get the idea. Thansk |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare if same month in cell vs columns
I would like to show you what I mean, so let me try again. On one
worksheet(sheet #1) i have the months across the top, Jan thru Dec. (Jan 05 Feb 05 Mar 05.....Dec 05) On a second worksheet (sheet #2) I have a column of random dates of 05. Assume that in the column on sheet #2 there are 15 dates in May 05. I want to be able to count the dates in May and show them on Sheet#1 under May 05). Below you suggested =COUNT(IF(MONTH(Sheet2!D1:D20)=MONTH(C1),Sheet2!D1 :D20)) and it seems to return alot of errors. "RagDyer" wrote: Then try this *array* formula: =COUNT(IF(MONTH(Sheet2!D1:D20)=MONTH(C1),Sheet2!D1 :D20)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "MHY" wrote in message ... Sorry, I should have stated that both can be in the same format. mmm-yy, mm-dd-yr. Thanks "RagDyer" wrote: Assume Sheet1 column headers are plain text, while dates in Sheet2 are true dates, try this *array* formula: =COUNT(IF(TEXT(Sheet2!D1:D20,"mmmm")=C1,Sheet2!D1: D20)) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "MHY" wrote in message ... On sheet 1 I have the months across the top row and lets say that the month of March is in c1. On sheet 2 I have a column (d1:d20) with many dates, and I want to count the number of dates in the month of March. I think it would go something like this, =countif(month(sheet 2(d1:d20)),sheet 1(c1)) I may not have all of the () in the right place but hope you get the idea. Thansk |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare if same month in cell vs columns
First of all, this is an *array* formula, so, to repeat:
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Did you enter the formula correctly? Second, are the dates on both sheets *true* XL recognized dates, and *not* text entries that you keyed in? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "MHY" wrote in message ... I would like to show you what I mean, so let me try again. On one worksheet(sheet #1) i have the months across the top, Jan thru Dec. (Jan 05 Feb 05 Mar 05.....Dec 05) On a second worksheet (sheet #2) I have a column of random dates of 05. Assume that in the column on sheet #2 there are 15 dates in May 05. I want to be able to count the dates in May and show them on Sheet#1 under May 05). Below you suggested =COUNT(IF(MONTH(Sheet2!D1:D20)=MONTH(C1),Sheet2!D1 :D20)) and it seems to return alot of errors. "RagDyer" wrote: Then try this *array* formula: =COUNT(IF(MONTH(Sheet2!D1:D20)=MONTH(C1),Sheet2!D1 :D20)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "MHY" wrote in message ... Sorry, I should have stated that both can be in the same format. mmm-yy, mm-dd-yr. Thanks "RagDyer" wrote: Assume Sheet1 column headers are plain text, while dates in Sheet2 are true dates, try this *array* formula: =COUNT(IF(TEXT(Sheet2!D1:D20,"mmmm")=C1,Sheet2!D1: D20)) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "MHY" wrote in message ... On sheet 1 I have the months across the top row and lets say that the month of March is in c1. On sheet 2 I have a column (d1:d20) with many dates, and I want to count the number of dates in the month of March. I think it would go something like this, =countif(month(sheet 2(d1:d20)),sheet 1(c1)) I may not have all of the () in the right place but hope you get the idea. Thansk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 queries: Display last cell, and compare two lists | Excel Worksheet Functions | |||
Cell formating and Rolling columns | Excel Discussion (Misc queries) | |||
Truncating a columns worth of cells not just a single cell. | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
Syntax for inferred cell references | Excel Worksheet Functions |