Forum Discussion
How is this achievable?
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.
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.
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)
Lyuts Here's one way, using a cleaned-up version of your data and a pivot table using that same data.
