Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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,

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
Works perfectly. You are a gentlemen and a scholar, I really appreciate the qucik response too! Thanks –  Scheballs Jun 4 '13 at 20:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.