Conditional formula
 

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)

Conditional formula
 

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

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

--
Rick (MVP - Excel)



"nordiyu" wrote in message
...
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)

Conditional formula
 

hi
try this...
=SUMPRODUCT((D2:D10)*--(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)

Conditional formula
 

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

Fred

"FSt1" wrote in message
...
hi
try this...
=SUMPRODUCT((D2:D10)*--(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)

Conditional formula
 

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" wrote in message
...
You need either multiplication, or double unary. You don't need both. So
=SUMPRODUCT((D2:D10)*(C2:C10="yes")*(B2:B10="John" )*(MONTH(A2:A10)=1))
will do.

Fred

"FSt1" wrote in message
...
hi
try this...
=SUMPRODUCT((D2:D10)*--(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)

Conditional formula
 

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"),D1:D1000)

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")*D1:D1000)

--
Rick (MVP - Excel)



"nordiyu" wrote in message
...
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)

.

Conditional formula
 

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" wrote in message
...
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"),D1:D1000)

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")*D1:D1000)

--
Rick (MVP - Excel)



"nordiyu" wrote in message
...
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)

.

Conditional formula
 

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"),D1:D1000)

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")*D1:D1000)

--
Rick (MVP - Excel)



"nordiyu" wrote in message
...
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)

.