ExcelUser2022
New Member
- Joined
- Feb 4, 2022
- Messages
- 1
- Office Version
- 2021
- Platform
- Windows
I am having trouble getting my sumifs formula to add the entire criteria. It correctly adds the all the banking accounts individually (i.e. Dashboard!E4 w/o the transpose function) in the formula below, but when I reference named range "Bank" to include all of the accounts, it only adds the first bank account in the "Bank" range which is checking and the first Transaction type (i.e. Sale) . I cannot hard code it b/c the criteria will change over time. See below... Thanks!
Formula: SUM(SUMIFS(Amount,TYPE,CHOOSE({1,2,3},Sale,Pay_Partial_Invoice,Presale),CATEGORY,Settings!$K$2,ACCOUNT,TRANSPOSE(Bank),DATE,"<="&Settings!Z$4))
Named Ranges-
Amount: D2:D200
TYPE: C2:200
CATEGORY: E2:E200
Date: A2:A200
ACCOUNT:G2:G200
Dynamic Range listed on another sheet & their location in the workbook:
"Bank": Dashboard!E4:E11 (8 to 11 are currently blank, but the sheet will update automatically when another bank account is manually entered on another sheet)
Checking: located in Dashboard!E4
Savings: located in Dashboard!E5
Cash App: located in Dashboard!E5
PayPal: located in Dashboard!E6
Venmo: located in Dashboard!E7
Sale:
instant sale
Bulk Pymt
Pay_Partial_Invoice:
Pay Partial Invoice
Presale:
Prepaid Revenue
Answer comes up shows as 1 when I use Bank as a criteria instead of 10. Thanks again!
Formula: SUM(SUMIFS(Amount,TYPE,CHOOSE({1,2,3},Sale,Pay_Partial_Invoice,Presale),CATEGORY,Settings!$K$2,ACCOUNT,TRANSPOSE(Bank),DATE,"<="&Settings!Z$4))
Named Ranges-
Amount: D2:D200
TYPE: C2:200
CATEGORY: E2:E200
Date: A2:A200
ACCOUNT:G2:G200
Dynamic Range listed on another sheet & their location in the workbook:
"Bank": Dashboard!E4:E11 (8 to 11 are currently blank, but the sheet will update automatically when another bank account is manually entered on another sheet)
Checking: located in Dashboard!E4
Savings: located in Dashboard!E5
Cash App: located in Dashboard!E5
PayPal: located in Dashboard!E6
Venmo: located in Dashboard!E7
Sale:
instant sale
Bulk Pymt
Pay_Partial_Invoice:
Pay Partial Invoice
Presale:
Prepaid Revenue
Answer comes up shows as 1 when I use Bank as a criteria instead of 10. Thanks again!