Sum(vlookup)

NewMR

New Member
Joined
May 1, 2018
Messages
8
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
GL NoGL DescriptionDebitCredit
1001Petty Cash - EUR1000
1002Petty Cash USD200
1003Petty Cash - MXN2000
2200Inventory Dry Goods46
2201Inventory MF1200
2208Inventory MF2300
5010Payable USD72
5011Payable EUR100
5012Payable CAD70

<tbody>
</tbody>

Table 2
Cash3200
Inventory546
Payables242

<tbody>
</tbody>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
ABCD
1Table 1
2GL NoGL DescriptionDebitCredit
31001Petty Cash - EUR1000
41002Petty Cash USD200
51003Petty Cash - MXN2000
62200Inventory Dry Goods46
72201Inventory MF1200
82208Inventory MF2300
95010Payable USD72
105011Payable EUR100
115012Payable CAD70
12
13
14Table 2
15Cash3200
16Inventory546
17Payables242
Sheet91
Cell Formulas
RangeFormula
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")
 
Upvote 0
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?

GL OpenGL EndDescriptionTotal
10001099Cash=SUMIFS(Table2!L9:L59,Table2!A9:A115,">='BS Summary'!A10",Table2!A9:A115,"<='BS Summary'!B10")

<tbody>
</tbody>

Table 2 is in a separate worksheet.
ACL
1002Petty Cash USD1000
1015Petty Cash MXN2000
1020Bank - BoA1000

<tbody>
</tbody>
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top