I am trying to add a range of numbers only if the corresponding cells contain numerical values.

I am working on a grade tracking document where every assignment contains a different weighting. When students do not write the test an NA is entered. I would like to check which cells contain numerical values and add the corresponding weightings.

In the image above, the '%' column is automatically calculated and I would like this new formula to seek information from the 'Mark'column.

I tried this formula but it doesn't exclude NA and I am unsure how to include ISNUMBER to use this instead, Or maybe there is something else entirely I should be trying.

SUMPRODUCT(--(CHOOSE({1,2,3,4,5,6,7,8},B31,F31,J31,N31,R31,V31,Z31,AD31)>0),CHOOSE({1,2,3,4, 5,6,7,8},L5,L6,L7,L8,L9,L10,L11,L12))

L5 to L12 contain the weightings I would like added. L5 corresponds to B31, L6 to F31 and so on...

I would like to check if B31,F31,J31,N31,R31,V31,Z31,AD31 are numerical values and then add the corresponding cells in Column L.

CodePudding user response：

Are you using SUMPRODUCT because you need to multiply L5*B31,L6*F31.... and sum the result?? If that's so, you can use it like this:

=sumproduct(L5:L12,arrayformula(iferror({B31;F31;J31;N31;R31;V31;Z31;AD31},0)))

It it's a text or an error (that's why the arrayformula and iferror) it will be considered as "0". If "NA" is not an error and there aren't any chances of having one you can just use:

=sumproduct(L5:L12,{B31;F31;J31;N31;R31;V31;Z31;AD31})

Let me know if this is helpful!

Considering your given sample, you could divide this result by a sumif of the total marks of the tests the student actually took. With your new ranges that would be:

=sumproduct($H$4:$H$8,{C15;E15;G15;I15;K15})/sumif({C15;E15;G15;I15;K15},">0",$H$4:$H$8)

Or with the previous ones:

=sumproduct($L$5:$L$12,{B31;F31;J31;N31;R31;V31;Z31;AD31}/SUMIF({B31;F31;J31;N31;R31;V31;Z31;AD31},">0",$L$5:$L$12)

https://docs.google.com/spreadsheets/d/1wjvlK74nbUM7NfvG23fQL_T6fin6FL6YpYAxjsMV9sk/edit#gid=0