SUMIFS with VLOOKUP Across Multiple Workbooks

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
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

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.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,416
Messages
5,706,310
Members
421,441
Latest member
VapesRub

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
Top