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" wrote in message
...
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
"GregL" wrote in message
...
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:$D4/30/2010),('May YTD
Data'!$D:$D6/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