Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
..... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09, June-09 etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

=SUMPRODUCT(--(MONTH(B2:B6000)3),--(MONTH(B2:B6000)<7),--(YEAR(B2:B6000)=2009))


"Vijay DSK" wrote:

Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09, June-09 etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Hi,

On what column would you like to check for Apr-09, Jun-09 etc. Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09, June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Dear friend the result of your advice is as follows

15/04/2008 14/03/2009 0
16/04/2008 15/03/2009 0
17/04/2008 16/03/2009 0
18/04/2008 17/03/2009 0
19/04/2008 18/03/2009 0
20/04/2008 19/03/2009 0
21/04/2008 20/03/2009 0
22/04/2008 21/03/2009 0
23/04/2008 22/03/2009 0
24/04/2008 23/03/2009 0
25/04/2008 24/03/2009 0
26/04/2008 25/03/2009 0
27/04/2008 26/03/2009 0
28/04/2008 27/03/2009 0
29/04/2008 28/03/2009 0
30/04/2008 29/03/2009 0

Please advice....
"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH(B2:B6000)3),--(MONTH(B2:B6000)<7),--(YEAR(B2:B6000)=2009))


"Vijay DSK" wrote:

Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09, June-09 etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc. Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09, June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Hi,

Like Ashish has already pointed out there isn't enough information to answer
this:-

15/04/2008 14/03/2009 0
16/04/2008 15/03/2009 0
17/04/2008 16/03/2009 0
18/04/2008 17/03/2009 0
19/04/2008 18/03/2009 0

i want to count all the values under the month Apr-09, May-09, June-09 etc.

From the data posted above which dates are in April. Each pair of dates
covers and entire year!! What values are we counting?

Mike


"Vijay DSK" wrote:

Dear friend the result of your advice is as follows

15/04/2008 14/03/2009 0
16/04/2008 15/03/2009 0
17/04/2008 16/03/2009 0
18/04/2008 17/03/2009 0
19/04/2008 18/03/2009 0
20/04/2008 19/03/2009 0
21/04/2008 20/03/2009 0
22/04/2008 21/03/2009 0
23/04/2008 22/03/2009 0
24/04/2008 23/03/2009 0
25/04/2008 24/03/2009 0
26/04/2008 25/03/2009 0
27/04/2008 26/03/2009 0
28/04/2008 27/03/2009 0
29/04/2008 28/03/2009 0
30/04/2008 29/03/2009 0

Please advice....
"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH(B2:B6000)3),--(MONTH(B2:B6000)<7),--(YEAR(B2:B6000)=2009))


"Vijay DSK" wrote:

Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09, June-09 etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask the
question properly.
I want to find the number of cells in column2 basing on the month April.
example i found 16 cells in the example which i pasted in this post. Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc. Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09, June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Maybe this

=SUMPRODUCT(--(MONTH(A1:A16)=4))

Mike

"Vijay DSK" wrote:

Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask the
question properly.
I want to find the number of cells in column2 basing on the month April.
example i found 16 cells in the example which i pasted in this post. Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc. Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09, June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Give this a try for April 2009...

=SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009))

Change the 4 (month = April) and 2009 (year of interest) to suit your
conditions. I used 70000 rows to make sure I covered the 60000 rows
(records) you mentioned in your first posting... the number used (same in
both ranges) has to be larger than the number of actual records being
examined.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask the
question properly.
I want to find the number of cells in column2 basing on the month April.
example i found 16 cells in the example which i pasted in this post. Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc. Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09,
June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Rick,
When i tried that i am getting a "#value" error Help me out

"Rick Rothstein" wrote:

Give this a try for April 2009...

=SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009))

Change the 4 (month = April) and 2009 (year of interest) to suit your
conditions. I used 70000 rows to make sure I covered the 60000 rows
(records) you mentioned in your first posting... the number used (same in
both ranges) has to be larger than the number of actual records being
examined.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask the
question properly.
I want to find the number of cells in column2 basing on the month April.
example i found 16 cells in the example which i pasted in this post. Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc. Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09,
June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

I tested the formula before I posted it and it worked for me at that time.
Do you have *real* dates in Column B or are they text values (you can check
by looking at Format/Cells)?

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Rick,
When i tried that i am getting a "#value" error Help me out

"Rick Rothstein" wrote:

Give this a try for April 2009...

=SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009))

Change the 4 (month = April) and 2009 (year of interest) to suit your
conditions. I used 70000 rows to make sure I covered the 60000 rows
(records) you mentioned in your first posting... the number used (same in
both ranges) has to be larger than the number of actual records being
examined.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask the
question properly.
I want to find the number of cells in column2 basing on the month
April.
example i found 16 cells in the example which i pasted in this post.
Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc.
Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me
out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09,
June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Rick
My dates are *real* dates and for your information my date settings are in
dd/mm/yyyy.
Please look into this and advice.
Thanks once again

"Rick Rothstein" wrote:

I tested the formula before I posted it and it worked for me at that time.
Do you have *real* dates in Column B or are they text values (you can check
by looking at Format/Cells)?

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Rick,
When i tried that i am getting a "#value" error Help me out

"Rick Rothstein" wrote:

Give this a try for April 2009...

=SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009))

Change the 4 (month = April) and 2009 (year of interest) to suit your
conditions. I used 70000 rows to make sure I covered the 60000 rows
(records) you mentioned in your first posting... the number used (same in
both ranges) has to be larger than the number of actual records being
examined.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask the
question properly.
I want to find the number of cells in column2 basing on the month
April.
example i found 16 cells in the example which i pasted in this post.
Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc.
Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me
out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09,
June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

When Rick said that you could check by using Format/Cells, what I think he
meant is that if you use Format/ Cells to change the date format
temporarily, for example to dd mmm yyyy, if the display in the cell changes
they are real dates, but if they don't change they are text. Try that.

Also, remember to check all the rows in the column. If, for example, you
have a field description saying "date" in cell B1, this would give a #VALUE!
error from the formula.
You could check with =SUM(--(ISTEXT(B1:B70000))) as an *array formula*
entered with Control Shift Enter. It should give zero if you have no text
entries in your range.
--
David Biddulph

"Vijay DSK" wrote in message
...
Rick
My dates are *real* dates and for your information my date settings are in
dd/mm/yyyy.
Please look into this and advice.
Thanks once again

"Rick Rothstein" wrote:

I tested the formula before I posted it and it worked for me at that
time.
Do you have *real* dates in Column B or are they text values (you can
check
by looking at Format/Cells)?

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Rick,
When i tried that i am getting a "#value" error Help me out

"Rick Rothstein" wrote:

Give this a try for April 2009...

=SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009))

Change the 4 (month = April) and 2009 (year of interest) to suit your
conditions. I used 70000 rows to make sure I covered the 60000 rows
(records) you mentioned in your first posting... the number used (same
in
both ranges) has to be larger than the number of actual records being
examined.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask
the
question properly.
I want to find the number of cells in column2 basing on the month
April.
example i found 16 cells in the example which i pasted in this post.
Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc.
Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me
out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09,
June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Friends,
Thanks once again for the help. Rick, once again thanks, the formula advised
by you a master piece. Only mistake i did was there are some gaps in the
column which were filled by "-", so the formula doesn't worked out.

Any how thanks once again you people.

"David Biddulph" wrote:

When Rick said that you could check by using Format/Cells, what I think he
meant is that if you use Format/ Cells to change the date format
temporarily, for example to dd mmm yyyy, if the display in the cell changes
they are real dates, but if they don't change they are text. Try that.

Also, remember to check all the rows in the column. If, for example, you
have a field description saying "date" in cell B1, this would give a #VALUE!
error from the formula.
You could check with =SUM(--(ISTEXT(B1:B70000))) as an *array formula*
entered with Control Shift Enter. It should give zero if you have no text
entries in your range.
--
David Biddulph

"Vijay DSK" wrote in message
...
Rick
My dates are *real* dates and for your information my date settings are in
dd/mm/yyyy.
Please look into this and advice.
Thanks once again

"Rick Rothstein" wrote:

I tested the formula before I posted it and it worked for me at that
time.
Do you have *real* dates in Column B or are they text values (you can
check
by looking at Format/Cells)?

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Rick,
When i tried that i am getting a "#value" error Help me out

"Rick Rothstein" wrote:

Give this a try for April 2009...

=SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009))

Change the 4 (month = April) and 2009 (year of interest) to suit your
conditions. I used 70000 rows to make sure I covered the 60000 rows
(records) you mentioned in your first posting... the number used (same
in
both ranges) has to be larger than the number of actual records being
examined.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask
the
question properly.
I want to find the number of cells in column2 basing on the month
April.
example i found 16 cells in the example which i pasted in this post.
Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc.
Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help me
out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09, May-09,
June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

So you don't have an answer to your question yet then, right?

Give this array-entered** formula a try...

=SUM(IF(ISNUMBER(B1:B65535),MONTH(B1:B65535)=11,0) *IF(ISNUMBER(B1:B65535),YEAR(B1:B65535)=2008,0))

**Commit the formula with Ctrl+Shift+Enter, not just with Enter by itself.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...

Friends,
Thanks once again for the help. Rick, once again thanks, the formula
advised
by you a master piece. Only mistake i did was there are some gaps in the
column which were filled by "-", so the formula doesn't worked out.

Any how thanks once again you people.

"David Biddulph" wrote:

When Rick said that you could check by using Format/Cells, what I think
he
meant is that if you use Format/ Cells to change the date format
temporarily, for example to dd mmm yyyy, if the display in the cell
changes
they are real dates, but if they don't change they are text. Try that.

Also, remember to check all the rows in the column. If, for example, you
have a field description saying "date" in cell B1, this would give a
#VALUE!
error from the formula.
You could check with =SUM(--(ISTEXT(B1:B70000))) as an *array formula*
entered with Control Shift Enter. It should give zero if you have no
text
entries in your range.
--
David Biddulph

"Vijay DSK" wrote in message
...
Rick
My dates are *real* dates and for your information my date settings are
in
dd/mm/yyyy.
Please look into this and advice.
Thanks once again

"Rick Rothstein" wrote:

I tested the formula before I posted it and it worked for me at that
time.
Do you have *real* dates in Column B or are they text values (you can
check
by looking at Format/Cells)?

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Rick,
When i tried that i am getting a "#value" error Help me out

"Rick Rothstein" wrote:

Give this a try for April 2009...

=SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009))

Change the 4 (month = April) and 2009 (year of interest) to suit
your
conditions. I used 70000 rows to make sure I covered the 60000 rows
(records) you mentioned in your first posting... the number used
(same
in
both ranges) has to be larger than the number of actual records
being
examined.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask
the
question properly.
I want to find the number of cells in column2 basing on the month
April.
example i found 16 cells in the example which i pasted in this
post.
Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc.
Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in
message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help
me
out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09,
May-09,
June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Off course i got the answer for the problem by replacing the "-" with space
any how once again thanks for taking much pain in this. And another request
posted by me was at

"http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.worksheet.f unctions&tid=90b0d103-f35c-4a98-b56d-3a4525f50d60&cat=&lang=en&cr=US&sloc=&m=1&p=1"

can u just look into this and suggest me an idea
Thanks once again

"Rick Rothstein" wrote:

So you don't have an answer to your question yet then, right?

Give this array-entered** formula a try...

=SUM(IF(ISNUMBER(B1:B65535),MONTH(B1:B65535)=11,0) *IF(ISNUMBER(B1:B65535),YEAR(B1:B65535)=2008,0))

**Commit the formula with Ctrl+Shift+Enter, not just with Enter by itself.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...

Friends,
Thanks once again for the help. Rick, once again thanks, the formula
advised
by you a master piece. Only mistake i did was there are some gaps in the
column which were filled by "-", so the formula doesn't worked out.

Any how thanks once again you people.

"David Biddulph" wrote:

When Rick said that you could check by using Format/Cells, what I think
he
meant is that if you use Format/ Cells to change the date format
temporarily, for example to dd mmm yyyy, if the display in the cell
changes
they are real dates, but if they don't change they are text. Try that.

Also, remember to check all the rows in the column. If, for example, you
have a field description saying "date" in cell B1, this would give a
#VALUE!
error from the formula.
You could check with =SUM(--(ISTEXT(B1:B70000))) as an *array formula*
entered with Control Shift Enter. It should give zero if you have no
text
entries in your range.
--
David Biddulph

"Vijay DSK" wrote in message
...
Rick
My dates are *real* dates and for your information my date settings are
in
dd/mm/yyyy.
Please look into this and advice.
Thanks once again

"Rick Rothstein" wrote:

I tested the formula before I posted it and it worked for me at that
time.
Do you have *real* dates in Column B or are they text values (you can
check
by looking at Format/Cells)?

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Rick,
When i tried that i am getting a "#value" error Help me out

"Rick Rothstein" wrote:

Give this a try for April 2009...

=SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009))

Change the 4 (month = April) and 2009 (year of interest) to suit
your
conditions. I used 70000 rows to make sure I covered the 60000 rows
(records) you mentioned in your first posting... the number used
(same
in
both ranges) has to be larger than the number of actual records
being
examined.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask
the
question properly.
I want to find the number of cells in column2 basing on the month
April.
example i found 16 cells in the example which i pasted in this
post.
Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09 etc.
Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in
message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can help
me
out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different dates)

now i want to filter the data on Month wise for instance...
i want to count all the values under the month Apr-09,
May-09,
June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.

Post an example of what data you have and what you want.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Off course i got the answer for the problem by replacing the "-" with
space
any how once again thanks for taking much pain in this. And another
request
posted by me was at

"http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.worksheet.f unctions&tid=90b0d103-f35c-4a98-b56d-3a4525f50d60&cat=&lang=en&cr=US&sloc=&m=1&p=1"

can u just look into this and suggest me an idea
Thanks once again

"Rick Rothstein" wrote:

So you don't have an answer to your question yet then, right?

Give this array-entered** formula a try...

=SUM(IF(ISNUMBER(B1:B65535),MONTH(B1:B65535)=11,0) *IF(ISNUMBER(B1:B65535),YEAR(B1:B65535)=2008,0))

**Commit the formula with Ctrl+Shift+Enter, not just with Enter by
itself.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...

Friends,
Thanks once again for the help. Rick, once again thanks, the formula
advised
by you a master piece. Only mistake i did was there are some gaps in
the
column which were filled by "-", so the formula doesn't worked out.

Any how thanks once again you people.

"David Biddulph" wrote:

When Rick said that you could check by using Format/Cells, what I
think
he
meant is that if you use Format/ Cells to change the date format
temporarily, for example to dd mmm yyyy, if the display in the cell
changes
they are real dates, but if they don't change they are text. Try
that.

Also, remember to check all the rows in the column. If, for example,
you
have a field description saying "date" in cell B1, this would give a
#VALUE!
error from the formula.
You could check with =SUM(--(ISTEXT(B1:B70000))) as an *array formula*
entered with Control Shift Enter. It should give zero if you have no
text
entries in your range.
--
David Biddulph

"Vijay DSK" wrote in message
...
Rick
My dates are *real* dates and for your information my date settings
are
in
dd/mm/yyyy.
Please look into this and advice.
Thanks once again

"Rick Rothstein" wrote:

I tested the formula before I posted it and it worked for me at
that
time.
Do you have *real* dates in Column B or are they text values (you
can
check
by looking at Format/Cells)?

--
Rick (MVP - Excel)


"Vijay DSK" wrote in message
...
Rick,
When i tried that i am getting a "#value" error Help me out

"Rick Rothstein" wrote:

Give this a try for April 2009...

=SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009))

Change the 4 (month = April) and 2009 (year of interest) to suit
your
conditions. I used 70000 rows to make sure I covered the 60000
rows
(records) you mentioned in your first posting... the number used
(same
in
both ranges) has to be larger than the number of actual records
being
examined.

--
Rick (MVP - Excel)


"Vijay DSK" wrote in
message
...
Dear friends,
thanks for your speedy help and Mike my apologies if i didnot
ask
the
question properly.
I want to find the number of cells in column2 basing on the
month
April.
example i found 16 cells in the example which i pasted in this
post.
Like
wise on every month.
hope i am clear in asking this time.

Thanks once again





"Vijay DSK" wrote:


Dear ashish
i want to check up on "To" column ( ie., column 2)

"Ashish Mathur" wrote:

Hi,

On what column would you like to check for Apr-09, Jun-09
etc.
Please
clarify.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vijay DSK" wrote in
message
...
Hi all,
Thanks for the previous help.
Now i am struck with another problem hope you guys can
help
me
out.

My issue is i have two columns like
Col1 Col2
Date date
from to
15/04/2008 14/03/2009
.... and so on (Sixty thousand records with different
dates)

now i want to filter the data on Month wise for
instance...
i want to count all the values under the month Apr-09,
May-09,
June-09
etc.
Hope i am clear in asking the question.
A speedy help will be greatly appreciated.