Embed a 'match' statement in sumproduct?
 

Excel 2007
I'm trying to help a coworker who wants to generate a few summary pieces of
data (like a pivot table, but for only a few categories). The raw data
includes both valid and invalid product codes, so these summaries need to
exclude the rows with invalid codes and their associated data.

Sheet1: Location of this formula. Formula is looking for a total for a
specific location
Sheet2: Raw data including valid and invalid product code sales
Sheet3: List of invalid product codes

This formula works, but does not exclude the invalid products:
=SUMPRODUCT(('Sheet 2'!$C$2:$C$60000='Sheet 1'!$F7)*1,('Sheet
2'!$I$2:$I$60000)*1)
[Note: Sheet 1!F7 is the location code I'm trying to match]

So I try to add a match statement, to exclude invalid products (multiply
matches by zero):
=SUMPRODUCT(('Sheet 2'!$C$2:$C$60000='Sheet 1'!$F7)*1,('Sheet
2'!$I$2:$I$60000)*1,IF(ISERROR(MATCH('Sheet
2'!$G$2:$G$60000,Sheet3!B$1:B$100,0)),1,0)*1)

I can't tell if there is a problem in my syntax, or if Sumproduct
automatically thinks my match statement should be rows 2-60000 instead of
1-100, which would then mean it isn't working how I'd expect.

I welcome any suggestions!
Thank you,
Keith

Embed a 'match' statement in sumproduct?
 

**Maybe** this...

=SUMPRODUCT(--('Sheet 2'!$C$2:$C$60000='Sheet 1'!$F7),
--(ISNA(MATCH('Sheet 2'!$G$2:$G$60000,Sheet3!B$1:B$100,0))),
'Sheet 2'!$I$2:$I$60000)

--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...
Excel 2007
I'm trying to help a coworker who wants to generate a few summary pieces
of
data (like a pivot table, but for only a few categories). The raw data
includes both valid and invalid product codes, so these summaries need to
exclude the rows with invalid codes and their associated data.

Sheet1: Location of this formula. Formula is looking for a total for a
specific location
Sheet2: Raw data including valid and invalid product code sales
Sheet3: List of invalid product codes

This formula works, but does not exclude the invalid products:
=SUMPRODUCT(('Sheet 2'!$C$2:$C$60000='Sheet 1'!$F7)*1,('Sheet
2'!$I$2:$I$60000)*1)
[Note: Sheet 1!F7 is the location code I'm trying to match]

So I try to add a match statement, to exclude invalid products (multiply
matches by zero):
=SUMPRODUCT(('Sheet 2'!$C$2:$C$60000='Sheet 1'!$F7)*1,('Sheet
2'!$I$2:$I$60000)*1,IF(ISERROR(MATCH('Sheet
2'!$G$2:$G$60000,Sheet3!B$1:B$100,0)),1,0)*1)

I can't tell if there is a problem in my syntax, or if Sumproduct
automatically thinks my match statement should be rows 2-60000 instead of
1-100, which would then mean it isn't working how I'd expect.

I welcome any suggestions!
Thank you,
Keith

Embed a 'match' statement in sumproduct?
 

Keith,

Try

=SUMPRODUCT((Sheet2!$C$2:$C$60000=Sheet1!$F7)*(ISE RROR(MATCH(Sheet2!$G$2:$G$60000,Sheet3!B$1:B$100,0 )))*(Sheet2!$I$2:$I$60000))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"ker_01" wrote in message
...
Excel 2007
I'm trying to help a coworker who wants to generate a few summary pieces
of
data (like a pivot table, but for only a few categories). The raw data
includes both valid and invalid product codes, so these summaries need to
exclude the rows with invalid codes and their associated data.

Sheet1: Location of this formula. Formula is looking for a total for a
specific location
Sheet2: Raw data including valid and invalid product code sales
Sheet3: List of invalid product codes

This formula works, but does not exclude the invalid products:
=SUMPRODUCT(('Sheet 2'!$C$2:$C$60000='Sheet 1'!$F7)*1,('Sheet
2'!$I$2:$I$60000)*1)
[Note: Sheet 1!F7 is the location code I'm trying to match]

So I try to add a match statement, to exclude invalid products (multiply
matches by zero):
=SUMPRODUCT(('Sheet 2'!$C$2:$C$60000='Sheet 1'!$F7)*1,('Sheet
2'!$I$2:$I$60000)*1,IF(ISERROR(MATCH('Sheet
2'!$G$2:$G$60000,Sheet3!B$1:B$100,0)),1,0)*1)

I can't tell if there is a problem in my syntax, or if Sumproduct
automatically thinks my match statement should be rows 2-60000 instead of
1-100, which would then mean it isn't working how I'd expect.

I welcome any suggestions!
Thank you,
Keith

Embed a 'match' statement in sumproduct?
 

Thank you Biff and Bob- you are lifesavers!
Keith

"Bob Phillips" wrote:

Keith,

Try

=SUMPRODUCT((Sheet2!$C$2:$C$60000=Sheet1!$F7)*(ISE RROR(MATCH(Sheet2!$G$2:$G$60000,Sheet3!B$1:B$100,0 )))*(Sheet2!$I$2:$I$60000))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"ker_01" wrote in message
...
Excel 2007
I'm trying to help a coworker who wants to generate a few summary pieces
of
data (like a pivot table, but for only a few categories). The raw data
includes both valid and invalid product codes, so these summaries need to
exclude the rows with invalid codes and their associated data.

Sheet1: Location of this formula. Formula is looking for a total for a
specific location
Sheet2: Raw data including valid and invalid product code sales
Sheet3: List of invalid product codes

This formula works, but does not exclude the invalid products:
=SUMPRODUCT(('Sheet 2'!$C$2:$C$60000='Sheet 1'!$F7)*1,('Sheet
2'!$I$2:$I$60000)*1)
[Note: Sheet 1!F7 is the location code I'm trying to match]

So I try to add a match statement, to exclude invalid products (multiply
matches by zero):
=SUMPRODUCT(('Sheet 2'!$C$2:$C$60000='Sheet 1'!$F7)*1,('Sheet
2'!$I$2:$I$60000)*1,IF(ISERROR(MATCH('Sheet
2'!$G$2:$G$60000,Sheet3!B$1:B$100,0)),1,0)*1)

I can't tell if there is a problem in my syntax, or if Sumproduct
automatically thinks my match statement should be rows 2-60000 instead of
1-100, which would then mean it isn't working how I'd expect.

I welcome any suggestions!
Thank you,
Keith


.