Variable Criteria with SUMIFS Across Multiple Tables
I'm looking to make a variable table that sums up information based on the input.
I have two tables. One has the data I want to sum with individual department codes (Table1), and the other has department codes and the code they roll up into.
On a separate data set I'm working on, I have a filter function that pulls all unique roll up codes (C46 is one of the values from the filter) for a final report.
This formula should SUMIFS the amount on Table1 if the department code on that table matches the department codes on the Dept_Info table, pulling the department codes from the filtered roll up code (C46).
When I hard key it, this formula works:
=SUM(SUMIFS(Table1[Amount],Table1[Department],{1000,2000,3000,4000}))
But when I try to make it variable so I don't have to identify each department code, it doesn't work:
=SUM(SUMIFS(Table1[Amount],Table1[Department],"{"&TEXTJOIN(", ",TRUE,FILTER(Dept_Info[Dept ID],Dept_Info[Roll Up]=$C46))&"}"))
Any help would be appreciated!
Edit: Clarifying the request
I'm trying to identify the department IDs as a flexible variable, and I do not want to update the dept IDs manually because they change often, and it will be a lot of code to update across the entire workbook if that's the way this is approached. Here is some example data. FYI - C46 changed to F14 in this example.
The filter function picks up individual roll ups, and can be changed based on the information in the Dept_Info table. As I update that table, I would like the results from SUMIFS or SUM w/ Array to also be variable.
Unfortunately, these only work when I manually enter the dept IDs. How can I combine SUM or SUMIFS with something that will find all the different Dept IDs that match the filtered roll up information (F14) and sum the amounts on Table1?
[link] [comments]
Want to read more?
Check out the full article on the original site