Oct 06 2021 02:02 AM - edited Oct 06 2021 07:32 AM
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...
Oct 06 2021 02:30 AM
Oct 06 2021 07:03 AM
Oct 06 2021 07:16 AM
SolutionPlease 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.
Oct 06 2021 07:39 AM
Oct 06 2021 08:14 AM
@Lyuts Here's one way, using a cleaned-up version of your data and a pivot table using that same data.
Oct 06 2021 08:58 AM
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)
Oct 06 2021 07:16 AM
SolutionPlease 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.