CODEWITHSUNDEEP
excelgoogle-sheets
Joshua Owen
Joshua Owen

Reputation: 13

How to sum a range with multiple criteria where the condition is met from another sheet?

I have 3 sheets, a setup sheet, a transaction sheet, and a summary sheet. Setup sheet is formatted as such,

Account Type
Acct_1 Type_1
Acct_2 Type_1
Acct_3 Type_1
Acct_4 Type_2
Acct_5 Type_2
Acct_6 Type_2

Transaction Sheet

Account Amount Date
Acct_1 Cell 1
Acct_2 Cell 2
Acct_5 Cell 3
Acct_6 Cell 4
Acct_4 Cell 5
Acct_3 Cell 6

In the summary sheet I have a single cell that I want to sum up all transactions from the accounts with account type_2 before a selected date including the selected date. I know a vlookup can't do this. Any suggestions?

I've tried vlookup, and I got it to work by hard coding the wanted accounts in a sumifs, but I'd rather it update automatically, so if I add more accounts to the setup sheet.

Upvotes: 0

Views: 59

Answers (1)

Blindspots
Blindspots

Reputation: 1014

Formula

Assumes:

  • A1=lookup_type
  • A2=lookup_date
=LET(
   setup,setup!A:B,
   trans,transactions!A:C,
   type,A1, date,A2, 
   accts,FILTER(
     INDEX(setup,,1),
     INDEX(setup,,2)=type),
   SUM(
     FILTER(
       INDEX(trans,,2), 
       COUNTIF(
         accts, INDEX(trans,,1)), 
       1/(1/INDEX(trans,,3))<=date)))
  • 1 is divided by the inverse of each date in the transaction list as a minimal check that there is a non-zero number (date) in the cell. Otherwise blanks and zeros would also pass the test <=date

Upvotes: 0

Related Questions