把下图的数值先判断大于8的,然后把大于8的数值分别减去8,再把它们相加起来
方法一:
=SUMPRODUCT((A1:A7>8)*(A1:A7-8))
方法二:
=SUMIF(A1:A7,">8",A1:A7)-COUNTIF(A1:A7,">8")*8