Nov 21 2022 06:02 AM
Hi,
I am looking for one formula to count the points as explained in the attached workbook.
I can do it but I have use helper columns.
Thanks in advance.
Nov 21 2022 06:29 AM
=SUM(IF(B4=$I$3,$J$3,IF(ISNUMBER(SEARCH(B4,$I$3:$I$5)),1)),IF(C4=$I$4,$J$4,IF(ISNUMBER(SEARCH(C4,$I$3:$I$5)),1)),IF(D4=$I$5,$J$5,IF(ISNUMBER(SEARCH(D4,$I$3:$I$5)),1)))
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021 or Excel online.
Nov 21 2022 07:48 AM
Nov 21 2022 08:36 AM
Solution=SUM(IF(B4="",0,IF(B4=$I$3,$J$3,IF(ISNUMBER(SEARCH(B4,$I$3:$I$5)),1))),IF(C4="",0,IF(C4=$I$4,$J$4,IF(ISNUMBER(SEARCH(C4,$I$3:$I$5)),1))),IF(D4="",0,IF(D4=$I$5,$J$5,IF(ISNUMBER(SEARCH(D4,$I$3:$I$5)),1))))
You can try this formula. Unfortunately i didn't take into account empty cells in my previous suggestion.
Nov 21 2022 09:11 AM - edited Nov 21 2022 09:16 AM
@OliverScheurich alternatively
=MMULT(--ISNUMBER(MATCH(B4:D7,J3:J5,0)),{1;1;1})+MMULT(--(B4:D7=TRANSPOSE(J3:J5)),K3:K5-1)
note: Both of these formulas will count extra points if someone can put the same country in multiple times. For example Brazil, Brazil, Brazil would get 5 points (3 for correct in correct place + 1 + 1 for correct in wrong place).
Nov 21 2022 08:36 AM
Solution=SUM(IF(B4="",0,IF(B4=$I$3,$J$3,IF(ISNUMBER(SEARCH(B4,$I$3:$I$5)),1))),IF(C4="",0,IF(C4=$I$4,$J$4,IF(ISNUMBER(SEARCH(C4,$I$3:$I$5)),1))),IF(D4="",0,IF(D4=$I$5,$J$5,IF(ISNUMBER(SEARCH(D4,$I$3:$I$5)),1))))
You can try this formula. Unfortunately i didn't take into account empty cells in my previous suggestion.