Conditional formula

  • Thread starter Thread starter nordiyu
  • Start date Start date
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)
 
I think this formula will do what you want...

=SUMPRODUCT((TEXT(A1:A1000,"mmmyyyy")="Jan2010")*(B1:B1000="John")*(C1:C1000="Yes")*D1:D1000)
 
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
 
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
 
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

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 ...
 
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.
 
Sir,
Thank for yr respon.
how to find out who a the highest amount for jan 2010

Month Name Amount
Jan 2010 ? ?

tq
nordiyu
 
Back
Top