Date Name Approved Total
12-Jan-10 John Yes $120.00
15-Jan-10 Mike Yes $50.00
20-Jan-10 John No $300.00
31-Jan-10 John Yes $80.00
02-Feb-10 Mike Yes $50.00
05-Feb-10 John Yes $180.00


Sir,
Needs yr help to formulated how to find out the approved name with highest
(total $) for the month jan 2010. (expected result for above condition : Jan
2010 - John - $200.00)

I think this formula will do what you want...

=SUMPRODUCT((TEXT(A1:A1000,"mmmyyyy")="Jan2010")*(B1:B1000="John")*(C1:C1000="Yes")*D11000)

--
Rick (MVP - Excel)



"nordiyu" <(E-Mail Removed)> wrote in message
news:59A8885C-62AF-448B-B4E4-(E-Mail Removed)...
> Date Name Approved Total
> 12-Jan-10 John Yes $120.00
> 15-Jan-10 Mike Yes $50.00
> 20-Jan-10 John No $300.00
> 31-Jan-10 John Yes $80.00
> 02-Feb-10 Mike Yes $50.00
> 05-Feb-10 John Yes $180.00
>
>
> Sir,
> Needs yr help to formulated how to find out the approved name with highest
> (total $) for the month jan 2010. (expected result for above condition :
> Jan
> 2010 - John - $200.00)

hi
try this...
=SUMPRODUCT((D210)*--(C2:C10="yes")*--(B2:B10="John")*((MONTH(A2:A10)=1)))

careful. formula wraped .
where D = amount
and C = approved
and B = name
and A = date

adjust ranges to fit your data.

regards
FSt1
"nordiyu" wrote:

> Date Name Approved Total
> 12-Jan-10 John Yes $120.00
> 15-Jan-10 Mike Yes $50.00
> 20-Jan-10 John No $300.00
> 31-Jan-10 John Yes $80.00
> 02-Feb-10 Mike Yes $50.00
> 05-Feb-10 John Yes $180.00
>
>
> Sir,
> Needs yr help to formulated how to find out the approved name with highest
> (total $) for the month jan 2010. (expected result for above condition : Jan
> 2010 - John - $200.00)

You need either multiplication, or double unary. You don't need both. So
=SUMPRODUCT((D210)*(C2:C10="yes")*(B2:B10="John")*(MONTH(A2:A10)=1))
will do.

Fred

"FSt1" <(E-Mail Removed)> wrote in message
news:49F98E89-C9CC-4D40-BC44-(E-Mail Removed)...
> hi
> try this...
> =SUMPRODUCT((D210)*--(C2:C10="yes")*--(B2:B10="John")*((MONTH(A2:A10)=1)))
>
> careful. formula wraped .
> where D = amount
> and C = approved
> and B = name
> and A = date
>
> adjust ranges to fit your data.
>
> regards
> FSt1
> "nordiyu" wrote:
>
>> Date Name Approved Total
>> 12-Jan-10 John Yes $120.00
>> 15-Jan-10 Mike Yes $50.00
>> 20-Jan-10 John No $300.00
>> 31-Jan-10 John Yes $80.00
>> 02-Feb-10 Mike Yes $50.00
>> 05-Feb-10 John Yes $180.00
>>
>>
>> Sir,
>> Needs yr help to formulated how to find out the approved name with
>> highest
>> (total $) for the month jan 2010. (expected result for above condition :
>> Jan
>> 2010 - John - $200.00)

To both FSt1 and Fred Smith.... I would be careful about using
(MONTH(A2:A10)=1) as one of the criteria because if the data spans more than
one year, you will pick up January results for all years listed.

--
Rick (MVP - Excel)



"Fred Smith" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You need either multiplication, or double unary. You don't need both. So
> =SUMPRODUCT((D210)*(C2:C10="yes")*(B2:B10="John")*(MONTH(A2:A10)=1))
> will do.
>
> Fred
>
> "FSt1" <(E-Mail Removed)> wrote in message
> news:49F98E89-C9CC-4D40-BC44-(E-Mail Removed)...
>> hi
>> try this...
>> =SUMPRODUCT((D210)*--(C2:C10="yes")*--(B2:B10="John")*((MONTH(A2:A10)=1)))
>>
>> careful. formula wraped .
>> where D = amount
>> and C = approved
>> and B = name
>> and A = date
>>
>> adjust ranges to fit your data.
>>
>> regards
>> FSt1
>> "nordiyu" wrote:
>>
>>> Date Name Approved Total
>>> 12-Jan-10 John Yes $120.00
>>> 15-Jan-10 Mike Yes $50.00
>>> 20-Jan-10 John No $300.00
>>> 31-Jan-10 John Yes $80.00
>>> 02-Feb-10 Mike Yes $50.00
>>> 05-Feb-10 John Yes $180.00
>>>
>>>
>>> Sir,
>>> Needs yr help to formulated how to find out the approved name with
>>> highest
>>> (total $) for the month jan 2010. (expected result for above condition :
>>> Jan
>>> 2010 - John - $200.00)
>

Rick

When referring the 1st row (header) the last * should be a , (comma) or else
it will return #value error

=SUMPRODUCT((TEXT(A1:A1000,"mmmyyyy")="Jan2010")*(B1:B1000="John")
*(C1:C1000="Yes"),D11000)

I am still not sure whether the OP is looking for this as the query is to
find out the approved ** name ** with highest total ...

--
Jacob (MVP - Excel)


"Rick Rothstein" wrote:

> I think this formula will do what you want...
>
> =SUMPRODUCT((TEXT(A1:A1000,"mmmyyyy")="Jan2010")*(B1:B1000="John")*(C1:C1000="Yes")*D11000)
>
> --
> Rick (MVP - Excel)
>
>
>
> "nordiyu" <(E-Mail Removed)> wrote in message
> news:59A8885C-62AF-448B-B4E4-(E-Mail Removed)...
> > Date Name Approved Total
> > 12-Jan-10 John Yes $120.00
> > 15-Jan-10 Mike Yes $50.00
> > 20-Jan-10 John No $300.00
> > 31-Jan-10 John Yes $80.00
> > 02-Feb-10 Mike Yes $50.00
> > 05-Feb-10 John Yes $180.00
> >
> >
> > Sir,
> > Needs yr help to formulated how to find out the approved name with highest
> > (total $) for the month jan 2010. (expected result for above condition :
> > Jan
> > 2010 - John - $200.00)
>
> .
>

Actually, I posted my "quick test" formula... I meant to change the Row 1
references to Row 2 references and make mention that I had assumed Row 1
contained headers, but I forgot to do so.

--
Rick (MVP - Excel)



"Jacob Skaria" <(E-Mail Removed)> wrote in message
news:AB1B9101-EE0E-4465-B138-(E-Mail Removed)...
> Rick
>
> When referring the 1st row (header) the last * should be a , (comma) or
> else
> it will return #value error
>
> =SUMPRODUCT((TEXT(A1:A1000,"mmmyyyy")="Jan2010")*(B1:B1000="John")
> *(C1:C1000="Yes"),D11000)
>
> I am still not sure whether the OP is looking for this as the query is to
> find out the approved ** name ** with highest total ...
>
> --
> Jacob (MVP - Excel)
>
>
> "Rick Rothstein" wrote:
>
>> I think this formula will do what you want...
>>
>> =SUMPRODUCT((TEXT(A1:A1000,"mmmyyyy")="Jan2010")*(B1:B1000="John")*(C1:C1000="Yes")*D11000)
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>>
>> "nordiyu" <(E-Mail Removed)> wrote in message
>> news:59A8885C-62AF-448B-B4E4-(E-Mail Removed)...
>> > Date Name Approved Total
>> > 12-Jan-10 John Yes $120.00
>> > 15-Jan-10 Mike Yes $50.00
>> > 20-Jan-10 John No $300.00
>> > 31-Jan-10 John Yes $80.00
>> > 02-Feb-10 Mike Yes $50.00
>> > 05-Feb-10 John Yes $180.00
>> >
>> >
>> > Sir,
>> > Needs yr help to formulated how to find out the approved name with
>> > highest
>> > (total $) for the month jan 2010. (expected result for above condition
>> > :
>> > Jan
>> > 2010 - John - $200.00)
>>
>> .
>>

Sir,
Thank for yr respon.
how to find out who a the highest amount for jan 2010

Month Name Amount
Jan 2010 ? ?

tq
nordiyu

"Jacob Skaria" wrote:

> Rick
>
> When referring the 1st row (header) the last * should be a , (comma) or else
> it will return #value error
>
> =SUMPRODUCT((TEXT(A1:A1000,"mmmyyyy")="Jan2010")*(B1:B1000="John")
> *(C1:C1000="Yes"),D11000)
>
> I am still not sure whether the OP is looking for this as the query is to
> find out the approved ** name ** with highest total ...
>
> --
> Jacob (MVP - Excel)
>
>
> "Rick Rothstein" wrote:
>
> > I think this formula will do what you want...
> >
> > =SUMPRODUCT((TEXT(A1:A1000,"mmmyyyy")="Jan2010")*(B1:B1000="John")*(C1:C1000="Yes")*D11000)
> >
> > --
> > Rick (MVP - Excel)
> >
> >
> >
> > "nordiyu" <(E-Mail Removed)> wrote in message
> > news:59A8885C-62AF-448B-B4E4-(E-Mail Removed)...
> > > Date Name Approved Total
> > > 12-Jan-10 John Yes $120.00
> > > 15-Jan-10 Mike Yes $50.00
> > > 20-Jan-10 John No $300.00
> > > 31-Jan-10 John Yes $80.00
> > > 02-Feb-10 Mike Yes $50.00
> > > 05-Feb-10 John Yes $180.00
> > >
> > >
> > > Sir,
> > > Needs yr help to formulated how to find out the approved name with highest
> > > (total $) for the month jan 2010. (expected result for above condition :
> > > Jan
> > > 2010 - John - $200.00)
> >
> > .
> >