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?

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?

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?

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