Excel SUM or COUNT based on related fields from two Tables

Question

I have two tables in Excel

The small table with 50 rows...tblGroups[#Headers]= Employee ID; Name; Group.

The second table with 3000+ rows...tblData[#Headers]= EventID; Date; Employee ID; Value

Now note that Value=1 always. They just have unique EventID numbers. I am trying to create a report that displays the count or sum of that Value for a specific Group.

Again Group is found in my small table and Value is found in the large table, but EmployeeID is found in both. Is there a way I can write a lookup to return the SUM or COUNT of Value in the large Table where it includes the EmployeeIDs from a certain group? Therby SUM or COUNT for the whole Group.

I can get the first EmployeeID in the Group using =SUMIFS(tblData[Value],tblData[EmployeeID],INDEX(tblGroups[EmployeeID],MATCH(F4,tblGroups[Group],0)))

where F4 = the Group I want to search but how do I make it continue through the list of IDs with that Group and keep adding up?

Now I know I can add a helper column to my large table using vlookup to add the Group to the row of the large table but I am asking if there is another way to do it without helper columns in my data table, mainly because I like impressing my boss and finding new ways to do this.

Here is a example file for Download

Thanks,

Answer 1

You can use this "array formula" in G4 copied down

=SUM(SUMIFS(tblData[Value],tblData[EmployeeID],IF(tblGroups[Group]=F4,tblGroups[EmployeeID])))

confirmed with CTRL+SHIFT+ENTER

The IF function returns an "array" of all the IDs for that group and then SUMIFS also returns an array so you need SUM wrapped around that to sum them all for the final result