I need help with this sumif formula:

[valmir]

Hello folks, I'd appreciate your help on this:
Originally, I had this formula:

=SUM(IF(ISNUMBER(SEARCH("missing", BS2:BS24)), IF(BT2:BT24="ꞁ", 1, IF(BT2:BT24="Ձ", 2, IF(BT2:BT24="Յ", 3, 0))), 0))

which works like a charm.
And then I wanted to include all the characters I have for this purpose, as follows:
ꞁ=1; Р=1; Č=1; Ձ=2; Յ=3; Ч=4; Տ=5; ⑥=6; ⑦=7; ⑧=8; ⑨=9; ⑩=10
And I got to this:

=SUM(IF(ISNUMBER(SEARCH("missing", BS2:BS24)),IF(AND(BT2:BT24={"ꞁ","Р","Č"}), 1, IF(BT2:BT24="Ձ", 2, IF(BT2:BT24="Յ", 3, IF(BT2:BT24="Ч", 4, IF(BT2:BT24="Տ", 5, IF(BT2:BT24="⑥", 6, IF(BT2:BT24="⑦", 7, IF(BT2:BT24="⑧", 8, IF(BT2:BT24="⑨", 9, IF(BT2:BT24="⑩", 10, 0))))))))))), 0))

When I try to enter the formula (after changing commas to semi-colons due to regional settings), excel offers to correct the formula as:

=SUM(IF(ISNUMBER(SEARCH("missing";BS2:BS24));IF(OR(BT2:BT24={"ꞁ";"Р";"Č"});1;IF(BT2:BT24="Ձ";2;IF(BT2:BT24="Յ";3;IF(BT2:BT24="Ч";4;IF(BT2:BT24="Տ";5;IF(BT2:BT24="⑥";6;IF(BT2:BT24="⑦";7;IF(BT2:BT24="⑧";8;IF(BT2:BT24="⑨";9;IF(BT2:BT24="⑩";10;0)))))))))));0)

And that's when I get the "N/A" error
Thanks in advance! (I also want the final result, if any, to be a negative number)

 

[valmir]

Got it:

=SUM(IF(ISNUMBER(SEARCH("missing";BS2:BS24));IF((BT2:BT24="ꞁ");1;IF((BT2:BT24="P")+(BT2:BT24="C");1;IF(BT2:BT24="Ձ";2;IF(BT2:BT24="Յ";3;IF(BT2:BT24="Ч";4;IF(BT2:BT24="Տ";5;IF(BT2:BT24="⑥";6;IF(BT2:BT24="⑦";7;IF(BT2:BT24="⑧";8;IF(BT2:BT24="⑨";9;IF(BT2:BT24="⑩";10;0))))))))))));0)

 

[DanteAmor]

You can simplify it like this:

=LET(a;BT2:BT24;SUM(IF(ISNUMBER(SEARCH("missing";BS2:BS24));IF((a="ꞁ")+(a="P")+(a="C");1;IF(a="Ձ";2;IF(a="Յ";3;IF(a="Ч";4;IF(a="Տ";5;IF(a="⑥";6;IF(a="⑦";7;IF(a="⑧";8;IF(a="⑨";9;IF(a="⑩";10;0)))))))))));0))

 

[DanteAmor]

Or simplify this way.
The symbols must be ordered in ascending order, not by the numerical value they represent, but by their alphabetical value.

=SUM(IF(ISNUMBER(SEARCH("missing",BS2:BS24)),IFERROR(LOOKUP(BT2:BT24,{"⑥","⑦","⑧","⑨","⑩","C","ꞁ","P","Ч","Ձ","Յ","Տ"},{6,7,8,9,10,1,1,1,4,2,3,5}),0)))

 

[valmir]

Or simplify this way.
The symbols must be ordered in ascending order, not by the numerical value they represent, but by their alphabetical value.

=SUM(IF(ISNUMBER(SEARCH("missing",BS2:BS24)),IFERROR(LOOKUP(BT2:BT24,{"⑥","⑦","⑧","⑨","⑩","C","ꞁ","P","Ч","Ձ","Յ","Տ"},{6,7,8,9,10,1,1,1,4,2,3,5}),0)))

Oh that is really great! The first one (with error) I got from AI website. The second one I got from a paid website and yours is simply the best. Tested and working. Thank you so much!