Sumproduct;3 criteria
in [General]
|
From: Don Guillett on 4 Jun 2010 16:04 OP didn't mention version so I gave one that works in BOTH -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Roger Govier" <roger(a)technology4nospamu.co.uk> wrote in message news:Oh4cS3$ALHA.4704(a)TK2MSFTNGP06.phx.gbl... > Quick heads up, Don > > You can use Entire columns in XL2007 > > However, I would not recommend it for use with Sumproduct. > It is a very processor intensive function, and does not have the built in > "intelligence of Sumif and Sumifs, which just calculate on the used range > of a column. > Giving it 1 million plus comparisons to do for every part of a Sumproduct > formula is going to slow the system down. > > Either create a Table or a Dynamic range, and give that to Sumproduct, > rather than whole columns. > > -- > > Regards > Roger Govier > > "Don Guillett" <dguillett1(a)gmail.com> wrote in message > news:#MdqAt$ALHA.4388(a)TK2MSFTNGP04.phx.gbl... >> Sumproduct may NOT use ENTIRE columns. try k2:k22 >> SUMPRODUCT(('May YTD Data'!$K2:$K22="New Customer"),('May YTD >> Data'!$AB2:$AB22=B6),(month('May YTD Data'!$D2:$D22)=5),'May YTD >> Data'!$Q2:$Q22) >> >> -- >> Don Guillett >> Microsoft MVP Excel >> SalesAid Software >> dguillett(a)gmail.com >> "GregL" <GregL(a)discussions.microsoft.com> wrote in message >> news:CC471883-57EC-41DF-9422-FB545F99DE1F(a)microsoft.com... >>> Hello, >>> >>> I need a formula that will sum the dollar value of a column if the >>> entries >>> meet 3 seperate criteria. >>> >>> The current formula I have (that returns a #NUM error) is: >>> >>> =SUMPRODUCT(('May YTD Data'!$K:$K="New Customer"),('May YTD >>> Data'!$AB:$AB=B6),('May YTD Data'!$D:$D>4/30/2010),('May YTD >>> Data'!$D:$D<6/1/2010),'May YTD Data'!$Q:$Q) >>> >>> Idea is to sum the sales volume (dollar amount) for "New Customer" per >>> sales >>> reps (B6) for the month of May. >>> >>> Thanks in advance for any advice you provide >> >> >> __________ Information from ESET Smart Security, version of virus >> signature database 5172 (20100604) __________ >> >> The message was checked by ESET Smart Security. >> >> http://www.eset.com >> >> >> > > __________ Information from ESET Smart Security, version of virus > signature database 5172 (20100604) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > |