K
ker_01
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
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