Formula Does Not Omit Criteria
 

First off thank you for looking at this formula and Happy Holidays to all.
I have this formula Array entered:
=SUM(IF('Priced BOM'!$H$3:$H$458<"Yes",IF('Priced
BOM'!$N$3:$N$458<"Purchase*",IF('Priced BOM'!$X$3:$X$458<"USAInfo",'Priced
BOM'!$AE$3:$AE$458,0

I want to exclude everything in cells N3:N458 that includes the word
"purchase". In cells N3:N458 there are different phrases like "Purchase
History, PO #....", "USAInfo", "Use..." and many others and what I want to do
is have the formula calculate everything except those cells that start with
the word "Purchase". I have used an asterisk after purchase and have tried
the formula with =SUM(IF('Priced BOM'!$H$3:$H$458<"Yes",IF(LEFT('Priced
BOM'!$N$3:$N$458<"Purchase",8, nad it doesn't seem to work. Does any one
have any ideas.

TIA
Joe

Hi
try
=SUM(IF('Priced BOM'!$H$3:$H$458<"Yes",IF(LEFT('Priced
BOM'!$N$3:$N$458,8)<"Purchase",......

--
Regards
Frank Kabel
Frankfurt, Germany
"Joe Gieder" schrieb im Newsbeitrag
...
First off thank you for looking at this formula and Happy Holidays to all.
I have this formula Array entered:
=SUM(IF('Priced BOM'!$H$3:$H$458<"Yes",IF('Priced
BOM'!$N$3:$N$458<"Purchase*",IF('Priced
BOM'!$X$3:$X$458<"USAInfo",'Priced
BOM'!$AE$3:$AE$458,0

I want to exclude everything in cells N3:N458 that includes the word
"purchase". In cells N3:N458 there are different phrases like "Purchase
History, PO #....", "USAInfo", "Use..." and many others and what I want to
do
is have the formula calculate everything except those cells that start
with
the word "Purchase". I have used an asterisk after purchase and have tried
the formula with =SUM(IF('Priced BOM'!$H$3:$H$458<"Yes",IF(LEFT('Priced
BOM'!$N$3:$N$458<"Purchase",8, nad it doesn't seem to work. Does any one
have any ideas.

TIA
Joe

Thank you. It works great. When there's someone else looking they can usually
find the problem.

Again thank you
Joe

"Frank Kabel" wrote:

Hi
try
=SUM(IF('Priced BOM'!$H$3:$H$458<"Yes",IF(LEFT('Priced
BOM'!$N$3:$N$458,8)<"Purchase",......

--
Regards
Frank Kabel
Frankfurt, Germany
"Joe Gieder" schrieb im Newsbeitrag
...
First off thank you for looking at this formula and Happy Holidays to all.
I have this formula Array entered:
=SUM(IF('Priced BOM'!$H$3:$H$458<"Yes",IF('Priced
BOM'!$N$3:$N$458<"Purchase*",IF('Priced
BOM'!$X$3:$X$458<"USAInfo",'Priced
BOM'!$AE$3:$AE$458,0

I want to exclude everything in cells N3:N458 that includes the word
"purchase". In cells N3:N458 there are different phrases like "Purchase
History, PO #....", "USAInfo", "Use..." and many others and what I want to
do
is have the formula calculate everything except those cells that start
with
the word "Purchase". I have used an asterisk after purchase and have tried
the formula with =SUM(IF('Priced BOM'!$H$3:$H$458<"Yes",IF(LEFT('Priced
BOM'!$N$3:$N$458<"Purchase",8, nad it doesn't seem to work. Does any one
have any ideas.

TIA
Joe