Sum Values in Table If Criteria Exists In Another Table

[colinbo]

I am trying to sum the values in one table based on the existence of the criteria in another table. See the attached worksheet. I am looking at SUMIFS as a starting point but hit an issue where I cannot seem to get the current criteria value to do the lookup on the other table.

Any thoughts?

[teylyn]

Hello,

you may need to insert a column in the sheet "Remaining work over time" to display the team for the task ID. Then you can use Sumifs or Sumproduct easily to sum the data.

Insert a column after A and enter this formula

Please Login or Register  to view this content.

Copy down.

Note that from row 19 onwards, the numbers are stored as text, hence the *1 in the formula to coerce them back to numeric values for the Match() function to work.

cheers,

[colinbo]

you may need to insert a column in the sheet "Remaining work over time" to display the team for the task ID

That's what I am trying to avoid. The task type in the cube does not have the team linked to it. The secondary sheet comes from an extra view I created to do the linking at the database level. Both data sheets are actually Pivot Tables (copied into normal cells for the sake of the example).

[Haseeb Avarakkan]

Hello Colinbo,

Try this in C2, copy down.

=HLOOKUP(A2,'Remaining Work over Time'!$B$1:$M$23,MATCH(VLOOKUP(B2,'Team-Task Links'!$A$2:$B$18,2,0),'Remaining Work over Time'!$A$1:$A$23,0),0)

There are 6 different Task ID for each team. This formula only return the first ID information.

[colinbo]

Hello Colinbo,

Try this in C2, copy down.

=HLOOKUP(A2,'Remaining Work over Time'!$B$1:$M$23,MATCH(VLOOKUP(B2,'Team-Task Links'!$A$2:$B$18,2,0),'Remaining Work over Time'!$A$1:$A$23,0),0)

There are 6 different Task ID for each team. This formula only return the first ID information.

I need the sum for the team for each day though?

[Haseeb Avarakkan]

In the third sheet 'Team-Task Links' If teams are grouped, tr this,

Please Login or Register  to view this content.

[colinbo]

I think there one word that best expresses the formula you threw together...

WOW!

Works like a charm and cuts out a whole raft of VBA so we can finally use Excel Web Access web parts with the sheet. Thanks so much!