How is this achievable?

[Lyuts]

Hello folks, 

A few moths ago I started a new job and I did for them a simple table with list menu, and then a table where you get summarized data, with SUMSIF, for each  menu item.

Now they want me to do one with three columns with lists and the summarized data should connected with the different lists..

I will have multiply sport events, which are repeated and in each one there'll be different athlete types, grouped by nationality.  In the end I need to have table which automatically present data of how many and what type of athletes had played in which event, with how many times and for how long. Can you advise me how I can make this work...

[#2]

What does your data look like precisely? Can you perhaps upload an (anonimized) copy of your workbook with some data and an example of what the report should look like?

[#3]

I uploaded an example, is it working?

[#4]

Yes, apart from the #REF errors in the left-hand table.

In your example, the table on the right is more or less a copy of the table on the left. It does not make clear what exactly your problem is.

[#5 · Accepted solution]

@Lyuts 

Please see the attached document. As i did, remove all the merged cells in your document and all error messages as well. Please make sure that entries in the cells colored in black are removed as well. Some of these cells have error messages or numbers "1" or "2". 

=SUMPRODUCT(($A$5:$A$69=$I29)*($B$5:$B$69=$J29)*($C$5:$C$69=$K29)*D$5:D$69)

Enter formula in cell L29 and copy across range L29:O100. It works fine in my excel sheet.

[#6]

Hi, I uploaded it without errors again. The thing is that I'll have multiply Olympic games' data 1983, 2004 for example and I want to calculate how many times and duration of game, male or female were the boxers participating (for example) divided by country for the Olympics as a whole

[#7]

@Lyuts Here's one way, using a cleaned-up version of your data and a pivot table using that same data.

[#8]

@Lyuts 

I understand that you want to add data (further olympic games/winter) to the left table and the "combinded results" table should add up the data. This is what formula does. However, it only works if you remove all the merged cells for "Sports event" and "nationality" in both tables (Please see attached file).

In addition you have to adapt formula according to the size of the left table:

=SUMPRODUCT(($A$5:$A$91=$I29)*($B$5:$B$91=$J29)*($C$5:$C$91=$K29)*D$5:D$91)

You could as well enter formula which works up to row 245 (in this case you don't have to adapt formula for every single olympic games/winter): 

=SUMPRODUCT(($A$5:$A$245=$I29)*($B$5:$B$245=$J29)*($C$5:$C$245=$K29)*D$5:D$245)