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-24 13:05, G wrote:
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

In B1 in Sheet1 enter:

=SUMIF(Sheet2!$A$1:$A$100,A1&CHAR(42),Sheet2!$B$1:$B$100)

Adjust the ranges to suit.

 

[G]

Wonderful!!!!

I knew there had to be another way....


Thanks you very much!
G

 

[simonf]

If the range is another workbook, does this work only when the linked workbook is opened? I get a #VALUE error when I close the linked workbook and try to save or recalculate the SUMIF workbook.

 

[simonf]

Also when I closed the SUMIF workbook and reopen it without opening the linked workbook, I get the #VALUE error.

 

[Aladin Akyurek]

On 2002-10-30 19:51, simonf wrote:
If the range is another workbook, does this work only when the linked workbook is opened? I get a #VALUE error when I close the linked workbook and try to save or recalculate the SUMIF workbook.

That's right.

=SUMIF('C:Data[TestWB3.xls]Sheet1'!$A$1:$A$7,"b",'C:Data[TestWB3.xls]Sheet1'!$B$1:$B$7)

rquires that TestWB3.xls is open.

There are 2 options if you want to work with closed workbooks...

[1] Use a formula that operates on arrays instead of SUMIF...

Either:

=SUMPRODUCT(('C:Data[TestWB3.xls]Sheet1'!$A$1:$A$7="b")+0,'C:Data[TestWB3.xls]Sheet1'!$B$1:$B$7)

Or:

{=SUM(IF('C:Data[TestWB3.xls]Sheet1'!$A$1:$A$7="b",'C:Data[TestWB3.xls]Sheet1'!$B$1:$B$7))}

[2] Let the workbook that you want to keep closed do the required conditional computation...The recipe is:

TestWB3 reads the criterion "b" from aaSumifToClosedWB SimonF.xls...

='[aaSumifToClosedWB SimonF.xls]Sheet1'!$A$7

which is in Sheet2, A1.

TestWB3 computes the required sum...

=IF(A1<>0,SUMIF(Sheet1!A1:A7,A1,Sheet1!B1:B7),"")

which is in Sheet2, A2.

And aaSumifToClosedWB SimonF.xls reads the compted result back...

=IF(LEN(A7),[TestWB3.xls]Sheet2!$A$2,"")

which is located in Sheet2, B7.

Under the foregoing recipe TestWB3.xls stays closed.

 

[simonf]

Ok, should this work?

=SUMPRODUCT(('C:Data[TestWB3.xls]Sheet1'!$A$1:$A$7=CHAR(42)&D5&CHAR(42))+0,'C:Data[TestWB3.xls]Sheet1'!$B$1:$B$7)

I get a 0 result. If I remove the CHAR(42), it works but how to include cells that only partially include D5?

 

[Aladin Akyurek]

On 2002-10-31 13:17, simonf wrote:
Ok, should this work?

=SUMPRODUCT(('C:Data[TestWB3.xls]Sheet1'!$A$1:$A$7=CHAR(42)&D5&CHAR(42))+0,'C:Data[TestWB3.xls]Sheet1'!$B$1:$B$7)

I get a 0 result. If I remove the CHAR(42), it works but how to include cells that only partially include D5?

Yes... Change the formula to...

=SUMPRODUCT((ISNUMBER(SEARCH(D2,'C:Data[TestWB3.xls]Sheet1'!$A$1:$A$7)))+0,'C:Data[TestWB3.xls]Sheet1'!$B$1:$B$7)

 

[simonf]

My final formula is this:

=SUMPRODUCT((ISNUMBER(SEARCH(CHAR(42)&D2&CHAR(42),'C:Data[TestWB3.xls]Sheet1'!$A$1:$A$7))*'C:Data[TestWB3.xls]Sheet1'!$B$1:$B$7)

Once again, the mighty Aladin came through. Mucho Gracias!

 

[simonf]

=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? Thanks.
This message was edited by simonf on 2002-10-31 18:45
This message was edited by simonf on 2002-10-31 18:46