How to CALCULATE assist points

[drumtechjp]

So, I realize this is well after the fact, but during the season, as we were developing a scouting database in excel, we were considering tracking each robot's individual assist locations per cycle, using a 3x3 grid as shown in the rules. A student had proposed that coding such a system would be VERY complex, given the myriad of possible outcomes. Due to circumstances of various symmetries, etc. I understand that this amount is pared significantly. Anyway, we decided on going a different route and didn't need it, but promised him that I would figure out a way to do so in the off season. I sat down last night and came up with a rather...... ugly bit of code in excel that will in fact calculate it correctly (included below), however, I was wondering if anyone else had done this, or had suggestions for a far more STREAMLINED approach.

The 3x3 grid occurs in the range C4:E6, and at the bottom of each column, as well as to the right of each row,there is a cell with a "counta" function pertaining to its respective adjacent column or row.

The code:
=IF(OR(AND(COUNTA(C4:E6)>5,(COUNTIF(C7:E7,">0")+CO UNTIF(F4:F6,">0")=6)),AND(COUNTIF(C7:E7,">0")+COUN TIF(F4:F6,">0")=6,(COUNTIF(C7:E7,"=3")+COUNTIF(F4: F6,"=3"))<>2,COUNTA(C4:E6)<>4)),30,IF(AND(COUNTIF( C7:E7,">0")+COUNTIF(F4:F6,">0")=6,COUNTA(C4:E6)=4, IFERROR(CELL("contents",INDIRECT(ADDRESS(MATCH(2,F 1:F6,0),MATCH(2,A7:E7,0)))),0)<>0),30,IF(AND(COUNT IF(C7:E7,">0")>1,COUNTIF(F4:F6,">0")>1),10,0)))


Basically, I began with scenarios to give 3 assists, then added elements to eliminate those which would incorrectly score 30 given the set parameters. I then proceeded to do the same with the 2 assist situations.

Any input would be greatly appreciated!