|
SUMIF or SUMPRODUCT or something else?
I would like to return the total amount of commission received per quarter
for different types of transactions. LISTINGS, SALES, REFERRALS, COMBOS Column 1 has the date of the transaction Column 2 has the TYPE Column 3 has the commission amount What I like to do is return the total commission per quarter, per type. Hence I am looking for a formula that would evaluate A1:A100 for a date between 1/1 and 3/31 *AND* B1:B100 for TYPES equal to LISTINGS, and then total all corresponding dollar amounts in Column C for the rows where column A & B meet the requirements 1/1 LIST 10,000 3/3 SALE 25,000 3/29 LIST 4,000 4/13 LIST 13,000 5/22 LIST 7,000 7/8 LIST 4,000 So in the example above, 1st QTR LIST TOTAL should return 14,000. I really don't know how to accomplish this and would appreciate any guidance. Thanks! -cypher |
=SUMPRODUCT(--(A1:A6="1/1/2004"+0),--(A1:A6<="3/31/2004"+0),--(B1:B6="List"),C1:C6) OR =SUMPRODUCT(--(A1:A6=D1),--(A1:A6<=E1),--(B1:B6=F1),C1:C6) ...where D1 is the start date, E1 the end date, and F1 the "Type" of interest. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 |
When embedding a date, I would suggest using
=SUMPRODUCT(--(A1:A6=--"2004/01/01"),--(A1:A6<=--"2004/03/31"),--(B1:B6="Li st"),C1:C6) it uses a more standard date format, and avoids any confusion with US and European dates. -- HTH RP (remove nothere from the email address if mailing direct) "Domenic" wrote in message ... =SUMPRODUCT(--(A1:A6="1/1/2004"+0),--(A1:A6<="3/31/2004"+0),--(B1:B6="List" ),C1:C6) OR =SUMPRODUCT(--(A1:A6=D1),--(A1:A6<=E1),--(B1:B6=F1),C1:C6) ..where D1 is the start date, E1 the end date, and F1 the "Type" of interest. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 |
Thanks Bob! I appreciate the advice! And yes, it can be confusing at times. :) Cheers mate! Bob Phillips Wrote: When embedding a date, I would suggest using =SUMPRODUCT(--(A1:A6=--"2004/01/01"),--(A1:A6<=--"2004/03/31"),--(B1:B6="Li st"),C1:C6) it uses a more standard date format, and avoids any confusion with US and European dates. -- HTH RP (remove nothere from the email address if mailing direct) "Domenic" wrote in message ... =SUMPRODUCT(--(A1:A6="1/1/2004"+0),--(A1:A6<="3/31/2004"+0),--(B1:B6="List" ),C1:C6) OR =SUMPRODUCT(--(A1:A6=D1),--(A1:A6<=E1),--(B1:B6=F1),C1:C6) ..where D1 is the start date, E1 the end date, and F1 the "Type" of interest. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 |
Won't work for Scandinavian dates though, may I suggest
=SUMPRODUCT(--(A1:A6=DATE(2004,1,1)),--(A1:A6<=DATE(2004,3,31)),--(B1:B6="List"),C1:C6) this way all the functions will be translated into local versions and work -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Domenic" wrote in message ... Thanks Bob! I appreciate the advice! And yes, it can be confusing at times. :) Cheers mate! Bob Phillips Wrote: When embedding a date, I would suggest using =SUMPRODUCT(--(A1:A6=--"2004/01/01"),--(A1:A6<=--"2004/03/31"),--(B1:B6="Li st"),C1:C6) it uses a more standard date format, and avoids any confusion with US and European dates. -- HTH RP (remove nothere from the email address if mailing direct) "Domenic" wrote in message ... =SUMPRODUCT(--(A1:A6="1/1/2004"+0),--(A1:A6<="3/31/2004"+0),--(B1:B6="List" ),C1:C6) OR =SUMPRODUCT(--(A1:A6=D1),--(A1:A6<=E1),--(B1:B6=F1),C1:C6) ..where D1 is the start date, E1 the end date, and F1 the "Type" of interest. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 |
Peo Sjoblom Wrote: Won't work for Scandinavian dates though, may I suggest =SUMPRODUCT(--(A1:A6=DATE(2004,1,1)),--(A1:A6<=DATE(2004,3,31)),--(B1:B6="List"),C1:C6) this way all the functions will be translated into local versions and work That's great! Thanks Peo! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 |
Why not Peo?
-- HTH RP (remove nothere from the email address if mailing direct) "Peo Sjoblom" wrote in message ... Won't work for Scandinavian dates though, may I suggest =SUMPRODUCT(--(A1:A6=DATE(2004,1,1)),--(A1:A6<=DATE(2004,3,31)),--(B1:B6="L ist"),C1:C6) this way all the functions will be translated into local versions and work -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Domenic" wrote in message ... Thanks Bob! I appreciate the advice! And yes, it can be confusing at times. :) Cheers mate! Bob Phillips Wrote: When embedding a date, I would suggest using =SUMPRODUCT(--(A1:A6=--"2004/01/01"),--(A1:A6<=--"2004/03/31"),--(B1:B6="Li st"),C1:C6) it uses a more standard date format, and avoids any confusion with US and European dates. -- HTH RP (remove nothere from the email address if mailing direct) "Domenic" wrote in message ... =SUMPRODUCT(--(A1:A6="1/1/2004"+0),--(A1:A6<="3/31/2004"+0),--(B1:B6="List" ),C1:C6) OR =SUMPRODUCT(--(A1:A6=D1),--(A1:A6<=E1),--(B1:B6=F1),C1:C6) ..where D1 is the start date, E1 the end date, and F1 the "Type" of interest. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 |
Perfect! Peo thank you very much!!
-cypher "Peo Sjoblom" wrote in message ... Won't work for Scandinavian dates though, may I suggest =SUMPRODUCT(--(A1:A6=DATE(2004,1,1)),--(A1:A6<=DATE(2004,3,31)),--(B1:B6="L ist"),C1:C6) this way all the functions will be translated into local versions and work -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Domenic" wrote in message ... Thanks Bob! I appreciate the advice! And yes, it can be confusing at times. :) Cheers mate! Bob Phillips Wrote: When embedding a date, I would suggest using =SUMPRODUCT(--(A1:A6=--"2004/01/01"),--(A1:A6<=--"2004/03/31"),--(B1:B6="Li st"),C1:C6) it uses a more standard date format, and avoids any confusion with US and European dates. -- HTH RP (remove nothere from the email address if mailing direct) "Domenic" wrote in message ... =SUMPRODUCT(--(A1:A6="1/1/2004"+0),--(A1:A6<="3/31/2004"+0),--(B1:B6="List" ),C1:C6) OR =SUMPRODUCT(--(A1:A6=D1),--(A1:A6<=E1),--(B1:B6=F1),C1:C6) ..where D1 is the start date, E1 the end date, and F1 the "Type" of interest. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=276136 |
| All times are GMT +1. The time now is 06:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com