Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct + wildcard
Any body able to help?
I have a summary sheet looking up from a data sheet I need to look up a company name and produce a value of work at a defined stage The problem is that a single company can sometimes work in partnership with one or more other companies - all hose company names are recorded in the same cell in the data sheet. I use range names for contractors; value; status I use the following function SUMIF(contractors,"*" & A4 &"*",value) which gives me the total value of the contractors work, even when in partnership I want to use SUMPRODUCT(--(status=D$2),--(contractors=$A4),value) to give me a value at a stage (status) - but I also need to expand the contractors=$A$4 to include wildcard ie "*" & $A$4 "*", but it does not work when I insert the wildcard elements - any ideas on what I am missing? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct + wildcard
One way, try something like this:
=SUMPRODUCT((status=D$2)*(ISNUMBER(FIND($A$4,contr actors)))*($A$4<""),value) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Saintsman" wrote: Any body able to help? I have a summary sheet looking up from a data sheet I need to look up a company name and produce a value of work at a defined stage The problem is that a single company can sometimes work in partnership with one or more other companies - all hose company names are recorded in the same cell in the data sheet. I use range names for contractors; value; status I use the following function SUMIF(contractors,"*" & A4 &"*",value) which gives me the total value of the contractors work, even when in partnership I want to use SUMPRODUCT(--(status=D$2),--(contractors=$A4),value) to give me a value at a stage (status) - but I also need to expand the contractors=$A$4 to include wildcard ie "*" & $A$4 "*", but it does not work when I insert the wildcard elements - any ideas on what I am missing? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct + wildcard
Max
This works OK - thanks very much "Max" wrote: One way, try something like this: =SUMPRODUCT((status=D$2)*(ISNUMBER(FIND($A$4,contr actors)))*($A$4<""),value) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Saintsman" wrote: Any body able to help? I have a summary sheet looking up from a data sheet I need to look up a company name and produce a value of work at a defined stage The problem is that a single company can sometimes work in partnership with one or more other companies - all hose company names are recorded in the same cell in the data sheet. I use range names for contractors; value; status I use the following function SUMIF(contractors,"*" & A4 &"*",value) which gives me the total value of the contractors work, even when in partnership I want to use SUMPRODUCT(--(status=D$2),--(contractors=$A4),value) to give me a value at a stage (status) - but I also need to expand the contractors=$A$4 to include wildcard ie "*" & $A$4 "*", but it does not work when I insert the wildcard elements - any ideas on what I am missing? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct + wildcard
Good to hear that, Saintsman !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Saintsman" wrote in message ... Max This works OK - thanks very much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT, COUNTIF and wildcard | Excel Worksheet Functions | |||
Wildcard character inside sumproduct | Excel Worksheet Functions | |||
wildcard in sumproduct? | Excel Worksheet Functions | |||
SUMPRODUCT with Wildcard | Excel Worksheet Functions | |||
Sumproduct with Wildcard * | Excel Worksheet Functions |