SOLVED

Adding a Column Filter to my current formula

Occasional Contributor

Good morning all! I'm trying to modify my current formula to filter results by a column value if it matches another table's column. My example dataset is attached, but here's the goal:

 

Table1

NamesZones1/1/20224/1/20227/1/202210/1/20221/1/20234/1/20237/1/2023
Name 1NS18.5151545455252
Name 1AZ18.5151545455252
Name 1PPV18.5151545455252
Name 2NS18.51518.533.533.540.540.5
Name 2SS18.51518.533.533.540.540.5
Name 2AZ18.51518.533.533.540.540.5
Name 3SS21.521.521.53435.535.535.5
Name 3AZ21.521.521.53435.535.535.5
Name 4NS17.517.517.54550.550.550.5
Name 4AZ17.517.517.54550.550.550.5
Name 5AZ1919193435.535.535.5
Name 6NS0063641.541.541.5
Name 6AZ0063641.541.541.5
Name 7NS002.532.532.532.532.5
Name 7AZ002.532.532.532.532.5
Name 7PPV002.532.532.532.532.5
Name 8NS0001520.520.520.5
Name 8SS0001520.520.520.5

 

Table 2

ProjectZoneStartCompleteSumAssigned
Project 1NS10/1/20209/1/202315Name 1
Project 1NS10/1/20209/1/202315Name 2
Project 1NS10/1/20209/1/202315Name 3
Project 1NS10/1/20209/1/202315Name 4
Project 1NS10/1/20209/1/202315Name 5
Project 2SS4/1/20213/1/20223.5Name 1
Project 2SS4/1/20213/1/20223.5Name 2
Project 3SS10/1/20219/1/20222.5Name 3
Project 3SS10/1/20219/1/20222.5Name 4
Project 4SS11/23/202112/1/20224Name 3
Project 4SS11/23/202112/1/20224Name 5
Project 5NS7/1/202212/1/20232.5Name 6
Project 5NS7/1/202212/1/20232.5Name 7
Project 6NS7/1/202212/1/20233.5Name 6
Project 6NS7/1/202212/1/20233.5Name 2
Project 7NS9/1/20227/1/202615Name 1
Project 7NS9/1/20227/1/202615Name 4
Project 7NS9/1/20227/1/202615Name 7
Project 7NS9/1/20227/1/202615Name 8
Project 7NS9/1/20227/1/202615Name 6
Project 7NS9/1/20227/1/202615Name 5
Project 8NS9/1/20227/1/202615Name 1
Project 8NS9/1/20227/1/202615Name 4
Project 8NS9/1/20227/1/202615Name 2
Project 8NS9/1/20227/1/202615Name 6
Project 8NS9/1/20227/1/202615Name 7
Project 8NS9/1/20227/1/202615Name 3
Project 9SS12/23/20229/28/20235.5Name 3
Project 9SS12/23/20229/28/20235.5Name 5
Project 9SS12/23/20229/28/20235.5Name 8
Project 9SS12/23/20229/28/20235.5Name 6
Project 9SS12/23/20229/28/20235.5Name 4
Project 10SS2/21/20236/20/20247Name 1
Project 10SS2/21/20236/20/20247Name 2

 

My current formula is:

=IFERROR(SUM(
MMULT(
--(Table2[Assigned] = Table1[@[Name]:[Name]] ),
SEQUENCE( COLUMNS( Table2[Assigned] ), , 1,0) ) *
Table2[[Sum]:[Sum]] *
( Table2[[Start]:[Start]] <=
DATEVALUE(Table1[[#Headers],[1/1/2022]] )
) *
( Table2[[Complete]:[Complete]] >=
DATEVALUE(Table1[[#Headers],[1/1/2022]] )
)
),"")

 

But I need it to say if Table 1's zone matches the zone in Table 2 for that person, then return value from Sum column, that way I get the value for the person for the respective zone. 

 

Thank you!

6 Replies

@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.

Riny_van_Eekelen_0-1644412899311.png

 

 

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]] )
)
)
best response confirmed by keltzjd (Occasional Contributor)
Solution

@keltzjd I used the file you sent earlier. Seems to work as expected. See Attached.

 

It did! Thank you so much!