Sumproduct using NOT equal to
 

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)

Sumproduct using NOT equal to
 

You cannot negated cell references
Also the size of each array must be the same
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ocuhcs" wrote in message
...
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)

Sumproduct using NOT equal to
 

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)

--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
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)

Sumproduct using NOT equal to
 

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

=sum(YourRange)-YourOtherFormula

Regards,
Fred.

"ocuhcs" wrote in message
...
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)

Sumproduct using NOT equal to
 

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
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)

Sumproduct using NOT equal to
 

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?


--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
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)

Sumproduct using NOT equal to
 

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" wrote:

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?


--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
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)

Sumproduct using NOT equal to
 

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!

--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
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)