SOLVED

Contributor

# Formula to calculate Points earned

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.

5 Replies

# Re: Formula to calculate Points earned

``=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.

thank you !

# Re: Formula to calculate Points earned

Hi,

When I remove the countries (leave blank) that somebody has written, I get 9 points. This is a possibility for guys who will fill nothing.
Sorry I did not inform earlier but I use office 2019 which works OK with the formula.
best response confirmed by A_SIRAT (Contributor)
Solution

# Re: Formula to calculate Points earned

``=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.

# Re: Formula to calculate Points earned

``=MMULT(--ISNUMBER(MATCH(B4:D7,J3:J5,0)),{1;1;1})+MMULT(--(B4:D7=TRANSPOSE(J3:J5)),K3:K5-1)``