Sumif / Multiple / Partial Match

[G]

Good Morning!

I was hoping someone could assist me with a formula. I may not be doing this correctly, but am having problems deciding if there is another way.
I have tried =left(sumif(....left(....)....
I may not have the format correct.
--------------------------------------------
Example:
Sheet 1
column A1 contains Store #
1985
2010 ect....
Sheet 2
column A1 Contains Store #
1985-1
1985-2
Column B1 Contains Amount $
$1,000
$3,000

I woould like a formula that will match the first 4 digits and sumif the values to the right.

Thanks in advance for your assistance.....
G

 

[Aladin Akyurek]

On 2002-10-31 18:44, simonf wrote:
=SUMPRODUCT((ISNUMBER(SEARCH(CHAR(42)&"interest"&CHAR(42), B9:B46)))*(ISNUMBER(SEARCH(CHAR(42)&"depreciation"&CHAR(42), B9:B46)))*(ISNUMBER(SEARCH(CHAR(42)&"amortization"&CHAR(42), B9:B46)))*D9:D46)

I tried using this to sum the values in D if the value in B containing the three categories "interest", "depreciation" and "amortization" but all I get is 0. If I do it by individual category, it returns the correct value. Do I need to do a SUM of each individual test or is my syntax incorrect? [...]

Try...

=SUMPRODUCT((ISNUMBER(SEARCH("interest", B9:B46))+ISNUMBER(SEARCH("depreciation", B9:B46))+ISNUMBER(SEARCH("amortization",B9:B46)))*D9:D46)

PS. With SEARCH we don't need the CHAR bit.

 

[simonf]

COOL!