Sumproduct using NOT equal to

[ocuhcs]

I have a sumproduct formula that i am trying to sum all cells in arow that
are not already included in my formula. I am sure this can be done by
incorporating the NOT funtion, but I do NOT know how to execute this as an
array.

Here is my attempt that returns a #value error.
=SUMPRODUCT("'Wholesale Orders'!E:E),--(=not(='Project
Forecast'!A6:A22),('Wholesale Orders'!F:F)

 

[Bernard Liengme]

You cannot negated cell references
Also the size of each array must be the same
best wishes

 

[T. Valko]

Is this what you want...

Sum column F if column E does not contain entries from A?

What version of Excel are you using? Unless you're using Excel 2007 you
can't use entire columns as range references with SUMPRODUCT.

=SUMPRODUCT(--(ISNA(MATCH('Wholesale Orders'!E1:E100,'Project
Forecast'!A6:A22,0))),'Wholesale Orders'!F1:F100)

 

[Fred Smith]

Just subtract the sum of the range from the numbers "already included", as
in:

=sum(YourRange)-YourOtherFormula

Regards,
Fred.

 

[ocuhcs]

Exactly - I want to sum column F if E is not equal to any of the values in
A6:A22.
I am using Excel 2003 and I have modified the forluma you posted to show
ranges of columns and not full columns.

The formula works except for the part that eliminates the entries equal to
A6:A22, it is now summing all positive values from column F (including the
rows with values = to A6:A22.

 

[T. Valko]

Consider this small sample:

...........A..........E..........F
1........A.........A..........1
2........D.........B..........2
3...................C..........3
4...................D..........4
5...................E..........5

Sum F if E does not contain entries from A.

=SUMPRODUCT(--(ISNA(MATCH(E1:E5,A1:A2,0))),F1:F5)

Result = 10

E2, E3 and E5 meet the criteria so the formula is summing F2, F3 and F5.

Using that sample data what result would you expect?

 

[ocuhcs]

You nailed it! (again) I simply copied the formula from your sample and
plugged in my data and it returned the correct value. I compared it to the
formula from your first post and they were identical???????
I read these boards quite frequently trying to find answers to my questions
before I ask and T.Valko must have at least a 95% success rate in solving
questions - Thanks.

 

[T. Valko]

T.Valko must have at least a 95% success rate in solving questions

I think that's kind of low. <grin>

Thanks for the feedback!