Conditional formula

[nordiyu]

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 Rothstein]

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

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

 

[FSt1]

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

 

[Fred Smith]

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

 

[Rick Rothstein]

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.

 

[Jacob Skaria]

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 ...

 

[Rick Rothstein]

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.

 

[nordiyu]

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

Month Name Amount
Jan 2010 ? ?

tq
nordiyu