A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sumproduct;3 criteria



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 04:17 PM posted to microsoft.public.excel.misc
gregl
external usenet poster
 
Posts: 10
Default Sumproduct;3 criteria

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
  #2  
Old June 4th, 2010, 04:29 PM posted to microsoft.public.excel.misc
Jackpot
external usenet poster
 
Posts: 28
Default Sumproduct;3 criteria

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)

"GregL" wrote:

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

  #3  
Old June 4th, 2010, 04:33 PM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 8,419
Default Sumproduct;3 criteria

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:$DDATE(2010,4,30))*('May YTD
Data'!$D:$DDATE(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.



"GregL" wrote:

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

  #4  
Old June 4th, 2010, 04:33 PM posted to microsoft.public.excel.misc
Don Guillett[_2_]
external usenet poster
 
Posts: 607
Default Sumproduct;3 criteria

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


  #5  
Old June 4th, 2010, 04:51 PM posted to microsoft.public.excel.misc
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Sumproduct;3 criteria

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



  #6  
Old June 4th, 2010, 09:04 PM posted to microsoft.public.excel.misc
Don Guillett[_2_]
external usenet poster
 
Posts: 607
Default Sumproduct;3 criteria

OP didn't mention version so I gave one that works in BOTH

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger Govier" wrote in message
...
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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.