ExcelBanter - Sumproduct;3 criteria

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Sumproduct;3 criteria (https://www.excelbanter.com/excel-discussion-misc-queries/265238-sumproduct%3B3-criteria.html)

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

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

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

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

Sumproduct;3 criteria