Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Numerous criteria in sumproduct
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 49
Default Numerous criteria in sumproduct
Is there a more compact way of writing the formula below, where comparison
cells contain dates and text?

=SUMPRODUCT((Product="ABC")*(RawData!$E1:$E10000= CalculatedData!B2)*(RawData!$E1:$E10000<=Calculate dData!B3)*(RawData!$G$1:$G$10000
<B15)*(RawData!$G$1:$G$10000 <B16)*(RawData!$G$1:$G$10000
<B17)*(RawData!$G$1:$G$10000 <B18)*(RawData!$G$1:$G$10000
<B19)*(RawData!$G$1:$G$10000 <B20)*(RawData!$G$1:$G$10000
<B21)*(RawData!$G$1:$G$10000 <B22)*(RawData!$G$1:$G$10000
<B23)*(RawData!$G$1:$G$10000 <B24)*(RawData!$G$1:$G$10000
<B25)*(RawData!$G$1:$G$10000 <B26)*(RawData!$G$1:$G$10000
<B27)*(RawData!$G$1:$G$10000 <B28))

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,718
Default Numerous criteria in sumproduct
Replace all the (RawData!$G$1:$G$10000 <B15 to B28) with the following:

(NOT(ISNUMBER(MATCH(RawData!$G$1:$G$10000,B15:B28, 0))))

I haven't test, but I think it will work


"adimar" wrote:

Is there a more compact way of writing the formula below, where comparison
cells contain dates and text?

=SUMPRODUCT((Product="ABC")*(RawData!$E1:$E10000= CalculatedData!B2)*(RawData!$E1:$E10000<=Calculate dData!B3)*(RawData!$G$1:$G$10000
<B15)*(RawData!$G$1:$G$10000 <B16)*(RawData!$G$1:$G$10000
<B17)*(RawData!$G$1:$G$10000 <B18)*(RawData!$G$1:$G$10000
<B19)*(RawData!$G$1:$G$10000 <B20)*(RawData!$G$1:$G$10000
<B21)*(RawData!$G$1:$G$10000 <B22)*(RawData!$G$1:$G$10000
<B23)*(RawData!$G$1:$G$10000 <B24)*(RawData!$G$1:$G$10000
<B25)*(RawData!$G$1:$G$10000 <B26)*(RawData!$G$1:$G$10000
<B27)*(RawData!$G$1:$G$10000 <B28))

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 49
Default Numerous criteria in sumproduct

It certainly worked :)
Thank you.

"Teethless mama" wrote:

Replace all the (RawData!$G$1:$G$10000 <B15 to B28) with the following:

(NOT(ISNUMBER(MATCH(RawData!$G$1:$G$10000,B15:B28, 0))))

I haven't test, but I think it will work


"adimar" wrote:

Is there a more compact way of writing the formula below, where comparison
cells contain dates and text?

=SUMPRODUCT((Product="ABC")*(RawData!$E1:$E10000= CalculatedData!B2)*(RawData!$E1:$E10000<=Calculate dData!B3)*(RawData!$G$1:$G$10000
<B15)*(RawData!$G$1:$G$10000 <B16)*(RawData!$G$1:$G$10000
<B17)*(RawData!$G$1:$G$10000 <B18)*(RawData!$G$1:$G$10000
<B19)*(RawData!$G$1:$G$10000 <B20)*(RawData!$G$1:$G$10000
<B21)*(RawData!$G$1:$G$10000 <B22)*(RawData!$G$1:$G$10000
<B23)*(RawData!$G$1:$G$10000 <B24)*(RawData!$G$1:$G$10000
<B25)*(RawData!$G$1:$G$10000 <B26)*(RawData!$G$1:$G$10000
<B27)*(RawData!$G$1:$G$10000 <B28))

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 35,218
Default Numerous criteria in sumproduct
And
=not(isnumber(...
could be
=iserror(...
too.

Teethless mama wrote:

Replace all the (RawData!$G$1:$G$10000 <B15 to B28) with the following:

(NOT(ISNUMBER(MATCH(RawData!$G$1:$G$10000,B15:B28, 0))))

I haven't test, but I think it will work

"adimar" wrote:

Is there a more compact way of writing the formula below, where comparison
cells contain dates and text?

=SUMPRODUCT((Product="ABC")*(RawData!$E1:$E10000= CalculatedData!B2)*(RawData!$E1:$E10000<=Calculate dData!B3)*(RawData!$G$1:$G$10000
<B15)*(RawData!$G$1:$G$10000 <B16)*(RawData!$G$1:$G$10000
<B17)*(RawData!$G$1:$G$10000 <B18)*(RawData!$G$1:$G$10000
<B19)*(RawData!$G$1:$G$10000 <B20)*(RawData!$G$1:$G$10000
<B21)*(RawData!$G$1:$G$10000 <B22)*(RawData!$G$1:$G$10000
<B23)*(RawData!$G$1:$G$10000 <B24)*(RawData!$G$1:$G$10000
<B25)*(RawData!$G$1:$G$10000 <B26)*(RawData!$G$1:$G$10000
<B27)*(RawData!$G$1:$G$10000 <B28))


--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default Numerous criteria in sumproduct
And
=not(isnumber(...
could be
=iserror(...

Or, ISNA(...

--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
And
=not(isnumber(...
could be
=iserror(...
too.

Teethless mama wrote:

Replace all the (RawData!$G$1:$G$10000 <B15 to B28) with the following:

(NOT(ISNUMBER(MATCH(RawData!$G$1:$G$10000,B15:B28, 0))))

I haven't test, but I think it will work

"adimar" wrote:

Is there a more compact way of writing the formula below, where
comparison
cells contain dates and text?

=SUMPRODUCT((Product="ABC")*(RawData!$E1:$E10000= CalculatedData!B2)*(RawData!$E1:$E10000<=Calculate dData!B3)*(RawData!$G$1:$G$10000
<B15)*(RawData!$G$1:$G$10000 <B16)*(RawData!$G$1:$G$10000
<B17)*(RawData!$G$1:$G$10000 <B18)*(RawData!$G$1:$G$10000
<B19)*(RawData!$G$1:$G$10000 <B20)*(RawData!$G$1:$G$10000
<B21)*(RawData!$G$1:$G$10000 <B22)*(RawData!$G$1:$G$10000
<B23)*(RawData!$G$1:$G$10000 <B24)*(RawData!$G$1:$G$10000
<B25)*(RawData!$G$1:$G$10000 <B26)*(RawData!$G$1:$G$10000
<B27)*(RawData!$G$1:$G$10000 <B28))


--

Dave Peterson


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMPRODUCT w/ 3 criteria jane Excel Worksheet Functions 3 November 9th 07 11:26 PM
sumproduct and IF criteria?? ferde Excel Discussion (Misc queries) 5 March 24th 07 06:11 PM
SUMPRODUCT - 2 Criteria Sam via OfficeKB.com Excel Worksheet Functions 23 February 10th 07 01:52 AM
Sumproduct 4 criteria Laura Excel Worksheet Functions 0 November 29th 06 11:02 PM
Sumproduct with two criteria Rob Excel Worksheet Functions 9 February 10th 06 03:15 AM


All times are GMT +1. The time now is 12:58 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement