SUMPRODUCT Criteria to Not Include Values within Column

**freybe06** · 04-11-2014, 09:56 AM

Hello Everyone,

This is a half formula/half vba question... Attached is an example of my issue. I'm looking at the formula in Sheet3, cell F5. You'll see that the criteria "--(Sheet2!I:I<>Sheet1!O:O)" does not seem to be working. I need the formula to not include any values from Sheet2!E:E, where the corresponding value in column I does not equal any value in Sheet1!O:O. Also, these values can change by user so its not as simple as just typing them in to the formula as an array...

That VBA part of this is that it's in a macro, so if there is any way to make this easier using code instead of a simple formula, please let me know.

Thanks for your help!

Book5.xlsx

**daddylonglegs** · 04-11-2014, 10:24 AM

Try something like this:

=SUMPRODUCT(--(Sheet2!A:A="JAN"),--(Sheet2!R:R=E5),--ISNA(MATCH(Sheet2!I:I,Sheet1!O$1:O$100,0)),Sheet2!E:E)

.....but probably better not to use whole column refs - that will make the formula slow

SUMPRODUCT Criteria to Not Include Values within Column

LinkBack

Thread Tools

Rate This Thread

Display

SUMPRODUCT Criteria to Not Include Values within Column

Re: SUMPRODUCT Criteria to Not Include Values within Column

Thread Information

Users Browsing this Thread

Similar Threads

Exclude values with specific criteria in an array and include for only month of Jan

SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

Include or Exclude Column while printing based on a criteria

How to make chart include only values from every 5th column?

Modify Sumproduct to include helper column

Bookmarks

Bookmarks

Posting Permissions