Sum Values in Table If Criteria Exists In Another Table

**colinbo** · 08-03-2011, 04:44 PM

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** · 08-03-2011, 05:16 PM

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

=INDEX('Team-Task Links'!$A$1:$A$18,MATCH('Remaining Work over Time'!A2*1,'Team-Task Links'!$B$1:$B$18,0))

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** · 08-03-2011, 05:19 PM

Originally Posted by teylyn

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 A** · 08-03-2011, 05:22 PM

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** · 08-03-2011, 05:30 PM

Originally Posted by Haseeb A

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 A** · 08-03-2011, 05:52 PM

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

=SUMPRODUCT(SUMIFS(INDEX('Remaining Work over Time'!$B$2:$M$23,0,MATCH($A2,'Remaining Work over Time'!$B$1:$M$1,0)),'Remaining Work over Time'!$A$2:$A$23,INDEX('Team-Task Links'!$B:$B,MATCH($B2,'Team-Task Links'!$A:$A,0)):INDEX('Team-Task Links'!$B:$B,MATCH($B2&"ZZZZ",'Team-Task Links'!$A:$A))))

**colinbo** · 08-04-2011, 12:18 PM

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!