SUMPRODUCT But With <> Criteria

J

Joe Gieder

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
 
F

Frank Kabel

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))
 
J

Joe Gieder

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top