Conditional formula

N

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)
 
R

Rick Rothstein

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

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

FSt1

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
 
F

Fred Smith

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
 
R

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

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"),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 ...
 
R

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top