Sum(vlookup)

[NewMR]

What is the easiest way to sum all "GL No" from 1000-1099 in range A2:D9 relating to cash in Table 1 and place total into cell A2 in Table 2? ...2200-2299 in "Inventory" on Table 2 and 5000-5099 in "Payable" on Table 2 . I have created a helper column but there must be a better way.....any help?

Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]GL No[/TD]
[TD]GL Description[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Petty Cash - EUR[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Petty Cash USD[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Petty Cash - MXN[/TD]
[TD]2000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2200[/TD]
[TD]Inventory Dry Goods[/TD]
[TD]46[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2201[/TD]
[TD]Inventory MF1[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2208[/TD]
[TD]Inventory MF2[/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5010[/TD]
[TD]Payable USD[/TD]
[TD][/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]5011[/TD]
[TD]Payable EUR[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]5012[/TD]
[TD]Payable CAD[/TD]
[TD][/TD]
[TD]70[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Cash[/TD]
[TD]3200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inventory[/TD]
[TD]546[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Payables[/TD]
[TD][/TD]
[TD]242[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

This is one way, I'm opting to use the GL No as the condition for Summing, I'd imagine it will be more "reliable" than to use the "key words" (i.e. Cash, Inventory, Payable); however, if you prefer to use the keywords, we can tweak the formulas using wildcards:

Book1
	A	B	C	D
1	Table 1
2	GL No	GL Description	Debit	Credit
3	1001	Petty Cash - EUR	1000
4	1002	Petty Cash USD	200
5	1003	Petty Cash - MXN	2000
6	2200	Inventory Dry Goods	46
7	2201	Inventory MF1	200
8	2208	Inventory MF2	300
9	5010	Payable USD		72
10	5011	Payable EUR		100
11	5012	Payable CAD		70
12
13
14	Table 2
15	Cash	3200
16	Inventory	546
17	Payables		242
Sheet91

Cell Formulas
Range	Formula
B15	=SUMIFS($C$3:$C$11,$A$3:$A$11,">=1000",$A$3:$A$11,"<=1099")
B16	=SUMIFS($C$3:$C$11,$A$3:$A$11,">=2200",$A$3:$A$11,"<=2299")
C17	=SUMIFS($D$3:$D$11,$A$3:$A$11,">=5000",$A$3:$A$11,"<=5099")

 

[NewMR]

SUMIFS - looking up between a range of values

Hi, Thank I tried this and it did not work. It gives me a #value error. Instead of using a value - I selected cell A10 (GL Open) and B10 (GL Close) as the range...What is wrong with the formula?

[TABLE="width: 500"]
<tbody>[TR]
[TD]GL Open[/TD]
[TD]GL End[/TD]
[TD]Description[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1099[/TD]
[TD]Cash[/TD]
[TD]=SUMIFS(Table2!L9:L59,Table2!A9:A115,">='BS Summary'!A10",Table2!A9:A115,"<='BS Summary'!B10")[/TD]
[/TR]
</tbody>[/TABLE]

Table 2 is in a separate worksheet.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]C[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Petty Cash USD[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1015[/TD]
[TD]Petty Cash MXN[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1020[/TD]
[TD]Bank - BoA[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Re: SUMIFS - looking up between a range of values

Your layout has changed, I'm not creating Tables to Test, but this should fix it:

=SUMIFS(Table2!L9:L59,Table2!A9:A115,">="&'BS Summary'!A10,Table2!A9:A115,"<="&'BS Summary'!B10)

 

[Joyner]

Re: SUMIFS - looking up between a range of values

Hello, maybe you don't want another option, but have you tried a Pivot Table, They are ideal for something like this.