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))

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))

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))

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

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