Forum Discussion
Excluding certain results from an Index and Match operation
It's a little bit clearer what you're trying to do. The index/match formula in your original post is indicative of a lookup function, not a sum function.
But, it still depends on whether you want to exclude the entries where the ID, Category, and Ctry match Sheet1, or exclude all entries on Sheet2 where the ID matches any of the ID's listed on Sheet1 regardless of Category and Ctry. I believe both scenarios yield the same results, but I don't know if that may always be true.
I attached a file with examples for both. Though the second example should yield the same results as what Sergei has already suggested.
=INDEX('Sheet2'!F:F,MATCH(1,('Sheet2'!C:C=[@category])*('Sheet2'!D:D=[@[ctry]])*('Sheet2'!B:B<>[@[ID]]),0))
JMB17 Thank you; however, that didn't quite work. I've attached a spreadsheet using the formula you suggested. I'd like to sum all of the values drawn from sheet 2 that match the category and ctry fields but excludes amounts related to the ID from Sheet 1. Suggestions would be appreciated.
It's a little bit clearer what you're trying to do. The index/match formula in your original post is indicative of a lookup function, not a sum function.
But, it still depends on whether you want to exclude the entries where the ID, Category, and Ctry match Sheet1, or exclude all entries on Sheet2 where the ID matches any of the ID's listed on Sheet1 regardless of Category and Ctry. I believe both scenarios yield the same results, but I don't know if that may always be true.
I attached a file with examples for both. Though the second example should yield the same results as what Sergei has already suggested.
- That was what I was looking for. Thanks!
Here
it could be
=SUMPRODUCT( ($B$2:$B$71=G2)* ($C$2:$C$71=H2)* (COUNTIF(Sheet1!$A$2:$A$11,$A$2:$A$71)=0)* $D$2:$D$71 )
