Forum Discussion
Solved
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. Thanks in advance.
=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.
=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 !
- 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.=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.
