Wild card formula

[Corey]

Okay, I'll try my best to explain this one...

I have several customers with the same name but with
different endings. For example:

ABC (Japan)
ABC Elec (USA)
ABC Corp (Korea)

On a query spreadsheet I set up, all one has to do is type
in the customers name and it will tally units by product,
time period and application. To do this my array formula
is:

=SUM(IF((Sheet1!$A$4:$A$1793=D$1)*(Sheet1!$H$4:$H$1793=$C3)
*(Sheet1!$B$4:$B$1793=$A3),Sheet1!$M$4:$M$1793))

Of the three conditions required, the third one is my
problem ($A3 refers to the customer name that one is
prompted to type in).

Is it possible to edit the formula or something in $A3
that will capture all three spellings of the company?
Currently it works fine for each individual spelling, but
I'd like to have an accumulated total for worldwide
business with them on this one model.

Any help is appreciated...I know this is a long one, sorry.

Corey

 

[Frank Kabel]

Hi
try (non array entered):
=SUMPRODUCT(--(Sheet1!$A$4:$A$1793=D$1),--(Sheet1!$H$4:$H$1793=$C3),--(
ISNUMBER(FIND($A3,Sheet1!$B$4:$B$1793))),Sheet1!$M$4:$M$1793)

And enter for example
'ABC'
in cell A3

 

[Bob Phillips]

Corey,

Could testing for ABC in the cell be okay

=SUM(IF((Sheet1!$A$4:$A$1793=D$1)*(Sheet1!$H$4:$H$1793=$C3)
*(NOT(ISERROR(FIND("ABC"),Sheet1!$B$4:$B$1793)))),Sheet1!$M$4:$M$1793))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Corey]

Excellent Frank!!! Thank you so much. I'll have to look
into using the SUMPRODUCT formula more often, as most of
my multi-conditional formulas are using an array, which I
think is slowing down my model for new data entry (I get
the Calculation % at the bottom upon enterint new
numbers). Thanks again! You guys are awesome.

 

[Corey]

Thanks for the response Bob. I was actually able to use
Frank's formula, so I didn't attempt yours yet. Gracias!

 

[Soo Cheon Jheong]

Corey,


=SUMPRODUCT((Sheet1!$A$4:$A$1793=D$1)*(Sheet1!$H$4:$H$1793=$C3)
*(LEFT(TRIM(Sheet1!$B$4:$B$1793),3)=$A3),Sheet1!$M$4:$M$1793)


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--