Sumproduct;3 criteria

  • Thread starter Thread starter GregL
  • Start date Start date
G

GregL

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
 
Try the below

=SUMPRODUCT(('May YTD Data'!$K:$K="New Customer")*
('May YTD Data'!$AB:$AB=B6)*
(TEXT('May YTD Data'!$D:$D,"mmmyyyy")="May2010"),'May YTD Data'!$Q:$Q)
 
Hi,

Try it this way but note you can only use full columns in E2007 and later.

With regard to the dates in your formula, Excel sees 4/30/2010 as 4 divided
by 30 divided by 2010 and not a date so note my change
Lastly I wouldn't put the dates in the formula I would reference them in a
cell the same as you have done for B6


=SUMPRODUCT(('May YTD Data'!$K:$K="New Customer")*('May YTD
Data'!$AB:$AB=B6)*('May YTD Data'!$D:$D>DATE(2010,4,30))*('May YTD
Data'!$D:$D<DATE(2010,6,1))*('May YTD Data'!$Q:$Q))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
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)
 
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 said:
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
(e-mail address removed)



__________ Information from ESET Smart Security, version of virus
signature database 5172 (20100604) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5172 (20100604) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Back
Top