|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 20th, 2010, 03:07 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) 
|
|
#2
April 20th, 2010, 04:00 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formula
I think this formula will do what you want...
=SUMPRODUCT((TEXT(A1:A1000,"mmmyyyy")="Jan2010")*( B1:B1000="John")*(C1:C1000="Yes")*D1  1000)-- 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)
|
|
#3
April 20th, 2010, 04:02 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formula
hi
try this... =SUMPRODUCT((D2 10)*--(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)
|
|
#4
April 20th, 2010, 04:40 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formula
You need either multiplication, or double unary. You don't need both. So
=SUMPRODUCT((D2 10)*(C2:C10="yes")*(B2:B10="John" )*(MONTH(A2:A10)=1))will do. Fred "FSt1" wrote in message ... hi try this... =SUMPRODUCT((D2 10)*--(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)
|
|
#5
April 20th, 2010, 05:50 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 10)*(C2:C10="yes")*(B2:B10="John" )*(MONTH(A2:A10)=1))will do. Fred "FSt1" wrote in message ... hi try this... =SUMPRODUCT((D2 10)*--(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)
|
|
#6
April 20th, 2010, 07:33 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 1000)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 1000)-- 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) .
|
|
#7
April 20th, 2010, 07:43 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 1000)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 1000)-- 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) .
|
|
#8
April 21st, 2010, 04:25 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 1000)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 1000)-- 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) .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
