Prev: Creating backgroound for an Invoice
Next: Max Function
From: dgnamu on 18 May 2010 12:21

hi,

can someone helps me to solve this problem?

name no books read month
nasir 1 12-jan-2010
adam 2 12-jan-2010
fatima 3 14-jan-2010
nasir 2 2-feb-2010
adam 1 2-feb-2010
fatima 1 3-feb-2010
nasir 1 7-mar-2010
adam 4 16-mar-2010
fatima 1 21-mar-2010
nasir 1 17-apr-2010
adam 4 18-apr-2010
fatima 1 24-apr-2010

now, what i'm doing is i'm just selecting the specific month and do the
counting. The formula is Sum(B2:B4) for january. this come to a problem if
the array is not sort accordingly.

what i want is, i'm trying to get the total no of book read in a specific
month like jan or mar. but the formula should cover from jan-apr. please
someone helps me to generate the formula.


From: Luke M on 18 May 2010 13:02
=SUMPRODUCT(--(MONTH(C2:C100)>=1),--(MONTH(C2:C100<=2),B2:B100)

Where 1 represents Jan, and 2 represents February. If you want only 1 months
data, change btoh of these numbers to the same value.

--
Best Regards,

Luke M
"dgnamu" <dgnamu(a)discussions.microsoft.com> wrote in message
news:1F6AF632-4619-4C5A-B0AD-F4992C6CE394(a)microsoft.com...
>
> hi,
>
> can someone helps me to solve this problem?
>
> name no books read month
> nasir 1 12-jan-2010
> adam 2 12-jan-2010
> fatima 3 14-jan-2010
> nasir 2 2-feb-2010
> adam 1 2-feb-2010
> fatima 1 3-feb-2010
> nasir 1 7-mar-2010
> adam 4 16-mar-2010
> fatima 1 21-mar-2010
> nasir 1 17-apr-2010
> adam 4 18-apr-2010
> fatima 1 24-apr-2010
>
> now, what i'm doing is i'm just selecting the specific month and do the
> counting. The formula is Sum(B2:B4) for january. this come to a problem if
> the array is not sort accordingly.
>
> what i want is, i'm trying to get the total no of book read in a specific
> month like jan or mar. but the formula should cover from jan-apr. please
> someone helps me to generate the formula.
>
>


 | 
Pages: 1
Prev: Creating backgroound for an Invoice
Next: Max Function