Google Sheets: Sum matching values across list of sheets

2
votes

I'm trying to put together an investment tracking sheet. For the overview page, I want to query and combine data from each of the account tabs but I'm having difficulty figuring out how to make that work.

Here's a link to my test sheet: https://docs.google.com/spreadsheets/d/14sZmxkM65ax9BKrkjinwOrOQPrS_xhqPPLs68Rggii4/edit

The formula I tried to use is in column J on the Overview tab. This formula works in Excel, but not Google Sheets:

=IF(F2<>"",SUMPRODUCT(SUMIF(INDIRECT("'"&Accounts&"'!"&"$N$2:$N$1000"),F2,INDIRECT("'"&Accounts&"'!"&"$O$2:$O$1000"))),"")
  • Accounts is a named range with the 4 account tab names
  • Column N on each account tab has the stock name
  • Column O on each account tab has the share count
  • Column F on the Overview tab has the stock names to match

If I run the same spreadsheet in Excel, the above formula works correctly and the share counts are correctly added from the 4 accounts: 120/20/50/101.

In google sheets I understand that INDIRECT doesn't support arrays, so it only shows the share counts from the first account: 100/20/0/0

So my question is if anyone can tell me how to modify the formulas from column J to work in Google Sheets?

Thank you.

1
google-sheets

1 Answers

0
votes

delete your whole column J and use this:

=ARRAYFORMULA(IFNA(VLOOKUP(F2:F, QUERY(
 {Account1!N:O; Account2!N:O; Account3!N:O; Account4!N:O}, 
 "select Col1,sum(Col2) 
  where Col1 is not null 
  group by Col1"), 2, 0)))

enter image description here