Dear All:

Please help I have put this formulas to count the times of Months

=SUMPRODUCT((MONTH(B6:B1613)=1)*(YEAR(B6:B1613)=2009)*(INT(B6:B1613)
<>INT(B7:B1614)))

but the result skip the duplicate date, how to solve this problem
please kindly help.

Thanking you in advance for kind help in this matter.

NM

Do you mean

=SUMPRODUCT((MONTH(B6:B1613)=1)*(YEAR(B6:B1613)=2009))

--
__________________________________
HTH

Bob

"Navarat Mishra" <(E-Mail Removed)> wrote in message
news:d8153e12-ec9c-4321-8064-(E-Mail Removed)...
> Dear All:
>
> Please help I have put this formulas to count the times of Months
>
> =SUMPRODUCT((MONTH(B6:B1613)=1)*(YEAR(B6:B1613)=2009)*(INT(B6:B1613)
> <>INT(B7:B1614)))
>
> but the result skip the duplicate date, how to solve this problem
> please kindly help.
>
> Thanking you in advance for kind help in this matter.
>
> NM

On Feb 23, 3:25*pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
> Do you mean
>
> =SUMPRODUCT((MONTH(B6:B1613)=1)*(YEAR(B6:B1613)=2009))
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Navarat Mishra" <navara...@gmail.com> wrote in message
>
> news:d8153e12-ec9c-4321-8064-(E-Mail Removed)...
>
>
>
> > Dear All:
>
> > Please help I have put this formulas to count the times of Months
>
> > =SUMPRODUCT((MONTH(B6:B1613)=1)*(YEAR(B6:B1613)=2009)*(INT(B6:B1613)
> > <>INT(B7:B1614)))
>
> > but the result skip the duplicate date, how to solve this problem
> > please kindly help.
>
> > Thanking you in advance for kind help in this matter.
>
> > NM- Hide quoted text -
>
> - Show quoted text -

Yes pls

input msg in column B
1 Jan 09
4 Jan 09
4 Jan 09
6 Jan 09


but by insert the formular as above the result comes 3 for Jan only
bcoz the duplicate of 4 Jan

Thank you.

Hi,

If you want the answer to be 4, then please use the following formula

SUMPRODUCT((YEAR(C4:C7)=2009)*(MONTH(C4:C7)=1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Navarat Mishra" <(E-Mail Removed)> wrote in message
news:7f26a10c-2ced-4a32-831b-(E-Mail Removed)...
> On Feb 23, 3:25 pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
>> Do you mean
>>
>> =SUMPRODUCT((MONTH(B6:B1613)=1)*(YEAR(B6:B1613)=2009))
>>
>> --
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "Navarat Mishra" <navara...@gmail.com> wrote in message
>>
>> news:d8153e12-ec9c-4321-8064-(E-Mail Removed)...
>>
>>
>>
>> > Dear All:
>>
>> > Please help I have put this formulas to count the times of Months
>>
>> > =SUMPRODUCT((MONTH(B6:B1613)=1)*(YEAR(B6:B1613)=2009)*(INT(B6:B1613)
>> > <>INT(B7:B1614)))
>>
>> > but the result skip the duplicate date, how to solve this problem
>> > please kindly help.
>>
>> > Thanking you in advance for kind help in this matter.
>>
>> > NM- Hide quoted text -
>>
>> - Show quoted text -
>
> Yes pls
>
> input msg in column B
> 1 Jan 09
> 4 Jan 09
> 4 Jan 09
> 6 Jan 09
>
>
> but by insert the formular as above the result comes 3 for Jan only
> bcoz the duplicate of 4 Jan
>
> Thank you.

On Feb 23, 6:08*pm, "Ashish Mathur" <mathurash...@hotmail.com> wrote:
> Hi,
>
> If you want the answer to be 4, then please use the following formula
>
> SUMPRODUCT((YEAR(C4:C7)=2009)*(MONTH(C4:C7)=1))
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVPwww.ashishmathur.com
>
> "Navarat Mishra" <navara...@gmail.com> wrote in message
>
> news:7f26a10c-2ced-4a32-831b-(E-Mail Removed)...
>
>
>
> > On Feb 23, 3:25 pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
> >> Do you mean
>
> >> =SUMPRODUCT((MONTH(B6:B1613)=1)*(YEAR(B6:B1613)=2009))
>
> >> --
> >> __________________________________
> >> HTH
>
> >> Bob
>
> >> "Navarat Mishra" <navara...@gmail.com> wrote in message
>
> >>news:d8153e12-ec9c-4321-8064-(E-Mail Removed)....
>
> >> > Dear All:
>
> >> > Please help I have put this formulas to count the times of Months
>
> >> > =SUMPRODUCT((MONTH(B6:B1613)=1)*(YEAR(B6:B1613)=2009)*(INT(B6:B1613)
> >> > <>INT(B7:B1614)))
>
> >> > but the result skip the duplicate date, how to solve this problem
> >> > please kindly help.
>
> >> > Thanking you in advance for kind help in this matter.
>
> >> > NM- Hide quoted text -
>
> >> - Show quoted text -
>
> > Yes pls
>
> > input msg in column B
> > 1 Jan 09
> > 4 Jan 09
> > 4 Jan 09
> > 6 Jan 09
>
> > but by insert the formular as above the result comes 3 for Jan only
> > bcoz the duplicate of 4 Jan
>
> > Thank you.- Hide quoted text -
>
> - Show quoted text -

Thank you

I have tried at home now and it seem to be worked,

I forgot what was the reason to put like that last time.

It is not showing error even there are blank cell.

Tomorrow will try at the office again, if got problem will ask again.

Thank you very much for today.