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
Data Workbook
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) |
---|---|
123 | 139.45 (desired result of formula) |
456 | 75.87 (desired result of formula) |
789 | 0.00 (desired result of formula) |
QRS | 185.92 (desired result of formula) |
Data Workbook
Account (column A) | Paid GT LL (column AE) | Paid Amount (column AJ) |
---|---|---|
456 | Y | 75.87 |
123 | N | 45.23 |
123 | Y | 45.22 |
789 | N | 0.00 |
QRS | Y | 185.92 |
123 | Y | 94.23 |