Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Sumif not Sumproduct | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |