double vlookup with count required..... I think!

[bankcott]

Hi - wondering if anyone can help. I have a large spreadsheet with lots of data on it which I have tried to replicate in the attachment. I want to then look at the departments and match those on sheet 2 and where the departments match then total up all the groups within that under each heading.

I have very basic knowledge of excel and think that this will involve more than one formula - would be delighted if anyone could help.

Thanks

[daffodil11]

Sounds like a SUMPRODUCT. There's only one tab on your attachment though.

[bankcott]

yes sorry I saved it as a csv in error. See if this one works. Can you tell me the formula I would write.

Thank you.

[daffodil11]

Which column are we adding up?

Are we counting the number of matches or the total No's?

[daffodil11]

Let me break SUMPRODUCT down for you the way we are using it:

=SUMPRODUCT((range1=criteria1)*(range2=criteria2))

Please Login or Register  to view this content.

This will total the matches for each Column B & Row 1 combination.

From C2 just drag that across and down.

[bankcott]

Hi - hope this makes sense. What I need to do is first check Sheet 1 Column B to find the department that matches in TEST (there can be several entries). If it does then I need it to look at C1 in Sheet 1 first of all and see if it matches anything in the TEST. If it does then I need to total these up to put the total in Sheet 1 Column C against the relevant department.

My test spreadsheet is very small compared to the data I am going to be running obviously.

thank you - appreciate any help you can give.

[daffodil11]

The solution I posted should work fine for that. It will count all of the matches.

[bankcott]

Excellent thanks, played around with this today and managed to get correct results - noted that the ranges needed to match.

Thank you so much for the help