SUMPRODUCT But With <> Criteria
 

I'm using this formula but I get the wrong answer,

=SUMPRODUCT(--('Priced BOM'!$H$3:$H$458<"Yes"),--(LEFT('Priced
BOM'!$N$3:$N$458,8)<"Purchase"),--('Priced
BOM'!$X$3:$X$458<"USAInfo"),--(ISNUMBER(SEARCH("Estimate",'Priced
BOM'!$X$3:$X$458))),('Priced BOM'!$AE$3:$AE$458))

What I want to do is where the formula says
--(ISNUMBER(SEARCH("Estimate",'Priced BOM'!$X$3:$X$458)))
what I realy would like to do is not include the cells that contain the word
"Estimate" But I can't figure it out. One thing the word "Estimate" can
either be in the front, middle or end of the string.

Thanks For Your Help
Joe

Hi
try:
=SUMPRODUCT(--('Priced BOM'!$H$3:$H$458<"Yes"),--(LEFT('Priced
BOM'!$N$3:$N$458,8)<"Purchase"),--('Priced
BOM'!$X$3:$X$458<"USAInfo"),--(ISERROR(SEARCH("Estimate",'Priced
BOM'!$X$3:$X$458))),('Priced BOM'!$AE$3:$AE$458))

--
Regards
Frank Kabel
Frankfurt, Germany
"Joe Gieder" schrieb im Newsbeitrag
...
I'm using this formula but I get the wrong answer,

=SUMPRODUCT(--('Priced BOM'!$H$3:$H$458<"Yes"),--(LEFT('Priced
BOM'!$N$3:$N$458,8)<"Purchase"),--('Priced
BOM'!$X$3:$X$458<"USAInfo"),--(ISNUMBER(SEARCH("Estimate",'Priced
BOM'!$X$3:$X$458))),('Priced BOM'!$AE$3:$AE$458))

What I want to do is where the formula says
--(ISNUMBER(SEARCH("Estimate",'Priced BOM'!$X$3:$X$458)))
what I realy would like to do is not include the cells that contain the
word
"Estimate" But I can't figure it out. One thing the word "Estimate" can
either be in the front, middle or end of the string.

Thanks For Your Help
Joe

Frank,
Thank you for your help, it works great. I need to learn more about what
functions you can string together and all the different functions and their
meanings. You are good.
Thanks again
Joe

"Frank Kabel" wrote:

Hi
try:
=SUMPRODUCT(--('Priced BOM'!$H$3:$H$458<"Yes"),--(LEFT('Priced
BOM'!$N$3:$N$458,8)<"Purchase"),--('Priced
BOM'!$X$3:$X$458<"USAInfo"),--(ISERROR(SEARCH("Estimate",'Priced
BOM'!$X$3:$X$458))),('Priced BOM'!$AE$3:$AE$458))

--
Regards
Frank Kabel
Frankfurt, Germany
"Joe Gieder" schrieb im Newsbeitrag
...
I'm using this formula but I get the wrong answer,

=SUMPRODUCT(--('Priced BOM'!$H$3:$H$458<"Yes"),--(LEFT('Priced
BOM'!$N$3:$N$458,8)<"Purchase"),--('Priced
BOM'!$X$3:$X$458<"USAInfo"),--(ISNUMBER(SEARCH("Estimate",'Priced
BOM'!$X$3:$X$458))),('Priced BOM'!$AE$3:$AE$458))

What I want to do is where the formula says
--(ISNUMBER(SEARCH("Estimate",'Priced BOM'!$X$3:$X$458)))
what I realy would like to do is not include the cells that contain the
word
"Estimate" But I can't figure it out. One thing the word "Estimate" can
either be in the front, middle or end of the string.

Thanks For Your Help
Joe