This is what I came up with
Formula:
=SUM(IF(ISERROR(LOOKUP(C3:AF3,{"a","B","c","d";1,2,3,4})),0,LOOKUP(C3:AF3,{"a","B","c","d";1,2,3,4})))
If you use named ranges:
Formula:
=SUM(IF(ISERROR(LOOKUP(C3:AF3,Code,Value)),0,LOOKUP(C3:AF3,Code,Value)))
Both are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
These formulae will work even if the letters are determined by formula and errors occur.
Bookmarks