# SUMIFS with VLOOKUP Across Multiple Workbooks

#### reberryjr

##### Well-known Member
I need some assistance using what I think would be a combination of the SUMIFS and VLOOKUP formulae. I would like to enter a formula in column B of the List workbook, that searches through all records on the Data workbook, and returns the sum of the Paid Amount where the value of the Paid GT LL = "Y", and the value of the Account column is the same. I'm unable to upload sample data (corporate laptop), but I've included a crude display of the workbooks I'm working with below. In the below example, I would want the formula to identify all records where the value of the Account is the same in both workbooks, then return the sum of the Paid Amount column, where the value of the Paid GT LL column = "Y".

I could probably use VBA to auto-filter on the value of the Account column in the Data workbook, then do a SUMIF formula in the List workbook; but I'd like to do this one with a formula, as my company has been making VBA increasingly more difficult to work with.

I've done some research on here, as well as Google, and haven't found anything that has really helped, so I appreciate any assistance you can provide.

Data Layout:
List Workbook
Account (column A)Paid GT LL (column B)
123139.45 (desired result of formula)
45675.87 (desired result of formula)
7890.00 (desired result of formula)
QRS185.92 (desired result of formula)

Data Workbook
Account (column A)Paid GT LL (column AE)Paid Amount (column AJ)
456Y75.87
123N45.23
123Y45.22
789N0.00
QRSY185.92
123Y94.23

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Fluff

##### MrExcel MVP, Moderator
You could just use sumifs like
Excel Formula:
``=SUMIFS(AJ:AJ,AE:AE,"Y",A:A,A2)``
you'll need to add the workbook & sheet names for the data workbook

#### reberryjr

##### Well-known Member
You could just use sumifs like
Excel Formula:
``=SUMIFS(AJ:AJ,AE:AE,"Y",A:A,A2)``
you'll need to add the workbook & sheet names for the data workbook
@Fluff Thank you as always!!!!! Works like a charm. I never knew you could just run it with a plain SUMIFS.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
3
Views
69
Replies
0
Views
268
Replies
3
Views
833
Replies
1
Views
215
Replies
9
Views
482

1,141,707
Messages
5,707,985
Members
421,539
Latest member
zuniBM

### 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.

### Which adblocker are you using?

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

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