SUMIF with LEFT and RIGHT Matrix

[Alohadboy]

Need help with a formula that would be a dynamic SUMIF of sorts. I have a dataset of GL codes (XX-XXXX-XXXXX) for which I need to sum amounts based on the values in two columns.


The left two segments (7 characters) represent the Business Unit and each Business Unit can have multiple GL Codes. To avoid complication, I would like to be able to sum all totals where the Business Unit matches and where the GL code is in the lookup range (Wages, Direct). Sure would be nice to post an example of the worksheet.


I think the formula would look something like this in cell C9, but this obviously doesn't work:


=SUMIFS(G9:G14, LEFT(F9:F14, 7)=B9, RIGHT(F9:F14, 5)=*C2:C6*)

 

[MUKESHY12390]

please share image of data and output.

 

[jtakw]

Hi,

Cross posted here: https://www.excelforum.com/excel-fo...f-with-left-and-right-matrix.html#post4909310

 

[shaowu459]

I can't open the website excelforum.

What stored in C2:C6?

I think the final solution seems like

=SUMPRODUCT(G9:G14*(LEFT(F9:F14,7)=B9)*(COUNTIF(C2:C6,"*"&F9:F14&"*")>0))

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G
1		GL Code	Wages	Direct
2		Driver	50100	50200
3		Admin	50101	50201
4		Overtime	50102	50202
5		Vacation	50103	50203
6		Bonus	50104	50204
7
8	Business Unit		AMOUNT	AMOUNT		DATA
9	Accounting	01-0100	1600	700		01-0100-50100	$500.00
10	Customer Service	01-0101	200	0		01-0100-50102	$100.00
11	Mgmt	01-0102	0	0		01-0100-50104	$100.00
12	Contracted Mgmt	01-0103	0	0		01-0101-50100	$200.00
13	Third Party	01-0104	0	0		01-0100-50202	$700.00
14						01-0100-50103	$900.00
Lookup

In C9 control+shift+enter, not just enter, copy across, and down:

=SUM(IF(ISNUMBER(MATCH($F$9:$F$14,$B9&"-"&C$2:C$6,0)),$G$9:$G$14))

 

[Alohadboy]

	A	B	C	D	E	F	G
GL Code
Driver
Admin
Overtime
Vacation
Bonus
Business Unit	DATA
Accounting	01-0100-50100
Customer Service	01-0100-50102
Mgmt	01-0100-50104
Contracted Mgmt	01-0101-50100
Third Party	01-0100-50202
01-0100-50103

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"] Wages [/TD]
[TD="align: center"] Direct [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] , align: center"]50100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] , align: center"]50200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] , align: center"]50101[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] , align: center"]50201[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] , align: center"]50102[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] , align: center"]50202[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] , align: center"]50103[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] , align: center"]50203[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] , align: center"]50104[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] , align: center"]50204[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: center"]AMOUNT[/TD]
[TD="align: center"]AMOUNT[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] "]01-0100[/TD]
[TD="align: right"]1600[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"][/TD]

[TD="align: right"] $500.00 [/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] "]01-0101[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"] $100.00 [/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] "]01-0102[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"] $100.00 [/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] "]01-0103[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"] $200.00 [/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=AEAAAA]#AEAAAA[/URL] "]01-0104[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"] $700.00 [/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"] $900.00 [/TD]

</tbody>

Lookup​

In C9 control+shift+enter, not just enter, copy across, and down:

=SUM(IF(ISNUMBER(MATCH($F$9:$F$14,$B9&"-"&C$2:C$6,0)),$G$9:$G$14))

Works perfectly. Thanks for the help.

 

[Aladin Akyurek]

Works perfectly. Thanks for the help.

You are welcome. Thanks for the update.