Forum Discussion
Adding a Column Filter to my current formula
keltzjd I used the file you sent earlier. Seems to work as expected. See Attached.
keltzjd I would recommend that you use the FILTER in stead of the a rather complicated combination of MMULT and SEQUENCE. The basic syntax is as follows:
=SUM(FILTER(Table2, (Assigned=Name)*(Zone=Zones)*(Start<=HeaderDate)*(Complete>=HeaderDate))
Since you already know how to make the structured references absolute and how to overcome the fact that the "HeaderDate" in Table1 is a text, I trust you can get it to work yourself.
But, I couldn't resist to create something in PowerQuery as well. See attached.
- I agree, but I've already built a dashboard that has this filter across 20 or so sheets, so I'm kind of stuck with using this formula lol. Do you know how to achieve it using my current formula? Thanks!
keltzjd I guess you can expand it with with one more rule. I.e. multiply by an array where the Zone in table2 agrees to the zone in table1. Similar to what you have now for finding matching names.
- This is what my formula looks like with that addition but it's not working, where am I getting it wrong?
=SUM(
MMULT(
--(Table57[[PM]:[CM5]] = Table27[@[Name]:[Name]] ),
SEQUENCE( COLUMNS( Table57[[PM]:[CM5]] ), , 1,0) ) *
(Table57[Zone] = Table27[@[Zone]:[Zone]] ),
SEQUENCE( COLUMNS( Table57[Zone] ), , 1,0) ) *
Table57[[SUM]:[SUM]] *
( Table57[[Start Date]:[Start Date]] <=
DATEVALUE(Table27[[#Headers],[1/1/2022]] )
) *
( Table57[[End Date]:[End Date]] >=
DATEVALUE(Table27[[#Headers],[1/1/2022]] )
)
)
