Sumproduct with wildcards
 

This formula works
=SUMPRODUCT(--(client=A$19),--(contractors=$A4),clientvalue)

How do I add wildcard *A$19* & *$A4*to the equation?
NB "*" & A$19 doesn't do it on it's own

Saintsman

Sumproduct with wildcards
 

I think that you will help an extra column with
=FIND($A$19,B4)0
for each cell and then doing sum product over the answer column.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Saintsman" wrote:

This formula works
=SUMPRODUCT(--(client=A$19),--(contractors=$A4),clientvalue)

How do I add wildcard *A$19* & *$A4*to the equation?
NB "*" & A$19 doesn't do it on it's own

Saintsman

Sumproduct with wildcards
 

Thanks for response
Managed to find a similar query in forum & amended that - used search function

"Martin Fishlock" wrote:

I think that you will help an extra column with
=FIND($A$19,B4)0
for each cell and then doing sum product over the answer column.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Saintsman" wrote:

This formula works
=SUMPRODUCT(--(client=A$19),--(contractors=$A4),clientvalue)

How do I add wildcard *A$19* & *$A4*to the equation?
NB "*" & A$19 doesn't do it on it's own

Saintsman

Sumproduct with wildcards
 

=SUMPRODUCT(--(ISNUMBER(SEARCH(A$19,client))),--(ISNUMBER(SEARCH($A4,contractors))),clientvalue)


"Saintsman" wrote:

This formula works
=SUMPRODUCT(--(client=A$19),--(contractors=$A4),clientvalue)

How do I add wildcard *A$19* & *$A4*to the equation?
NB "*" & A$19 doesn't do it on it's own

Saintsman