Sumproduct;3 criteria


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

New Reply
Thread Tools Rate Thread Display Modes
  #1  
Unread 4th June 2010, 16:19
GregL's Avatar
GregL
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Sumproduct;3 criteria
     Excel: General   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: General   

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

The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #2  
Unread 4th June 2010, 16:31
Jackpot's Avatar
Jackpot
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: Sumproduct;3 criteria

------ Register to get rid of these "In Post" ads! ------


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:$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


  #3  
Unread 4th June 2010, 16:34
Mike H's Avatar
Mike H
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: Sumproduct;3 criteria

------ Register to get rid of these "In Post" ads! ------


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.



"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:$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


The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #4  
Unread 4th June 2010, 16:36
Don Guillett's Avatar
Don Guillett
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Sumproduct;3 criteria

------ Register to get rid of these "In Post" ads! ------


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@gmail.com
"GregL" <GregL@discussions.microsoft.com> wrote in message
news:CC471883-57EC-41DF-9422-FB545F99DE1F@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



  #5  
Unread 4th June 2010, 16:53
Roger Govier's Avatar
Roger Govier
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Sumproduct;3 criteria

------ Register to get rid of these "In Post" ads! ------


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@gmail.com> wrote in message
news:#MdqAt$ALHA.4388@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@gmail.com
> "GregL" <GregL@discussions.microsoft.com> wrote in message
> news:CC471883-57EC-41DF-9422-FB545F99DE1F@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




  #6  
Unread 4th June 2010, 21:07
Don Guillett's Avatar
Don Guillett
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Sumproduct;3 criteria

------ Register to get rid of these "In Post" ads! ------


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Roger Govier" <roger@technology4nospamu.co.uk> wrote in message
news:Oh4cS3$ALHA.4704@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@gmail.com> wrote in message
> news:#MdqAt$ALHA.4388@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@gmail.com
>> "GregL" <GregL@discussions.microsoft.com> wrote in message
>> news:CC471883-57EC-41DF-9422-FB545F99DE1F@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
>
>
>



The Code Cage Advertisment
Advertisement
New Reply
The Code Cage at Facebookhe Code Cage at Twitter


Tags
criteria, sumproduct3

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 20:24.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University