vLookup the sum of multiple values from multiple criterias

[jos283]

Please see attached. If anyone could solve this, it would be greatly appreciated!

The highlighted cells in Summary tab should show the total training hours spent per person per activity pulled from the Data tab.
i.e. for Adam/Basketball, the cell should show 9 (from 4+5); David/Tennis = 3 (from just 3); Michael/Swimming = 16 (from 4+3+1+8), etc.

For those that don't appear i.e. Sherry/Swimming, it should show a sum of 0 instead of N/A if possible.

Not sure how to use the combination of vLookup or lookup, with "sum", "if" or "and" statements.

Spent quite a lot of time trying to crack this but couldn't... there are two tables I need help with.

Thank you for any resolutions! And if you could do it in the attachment and upload it so I can see what was done and edit it, that would be even better!

[jeffreybrown]

In B4 copied across and down >> =SUMIFS(Data!$C$2:$C$15,Data!$A$2:$A$15,B$3,Data!$B$2:$B$15,$A4)

But, have you considered a Pivot Table?

B10 copied down >> =SUM(SUMIFS(Data!$C$2:$C$15,Data!$A$2:$A$15,$A10,Data!$B$2:$B$15,{"Basketball","Tennis"}))

C10 copied down >> =SUM(SUMIFS(Data!$C$2:$C$15,Data!$A$2:$A$15,$A10,Data!$B$2:$B$15,{"Basketball","Swimming","Soccer"}))

[FlameRetired]

Administrative Note:

Welcome to the forum.

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here. I am, therefore, closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-for...ml#post5103453