I need to sum values from multiple instances of a lookup value in excel

user_19819606 0
Viewed 27

Hi I'm sorry if another thread answers this but I've tried looking and couldn't find anything helpful. I'm putting together a library database which involves 2 main pages, one for the stock of the books (how many are owned by the library) and one for the loan records.

Book Stock Page

1

Records Page

2

I want to be able to show in the stock page how many copies of a book are currently on loan. So in the record page it shows how many copies of a particular book a person has borrowed, but multiple people can borrow the same book at the same time. I've tried VLOOKUP and XLOOKUP but these only return the first instance of the book. My formulas look like this:

=XLOOKUP(A2,'Record of Loans'!A:A,'Record of Loans'!D:D)

Is there a way to return the value of each instance of the book title? Any help will be appreciated. It will also need an IF function or something to determine if the book has been returned, something like =IF(G2,0,D2)

excel-365 excel-formula
1 Answers

Want to share that I found how to do this on another site, I'll post the answer here in case it helps anyone in future.

The answer was to use SUMIFS which allows multiple criteria:

=SUMIFS('Record of Loans'!D:D,'Record of Loans'!A:A,'Library Stock'!A2,'Record of Loans'!G:G,"")

This only sums the number of copies on loan if the book title matches AND it hasn't been returned ('Record of Loans'!G:G,"")

user_198196060
Related