用excell计算无界细胞的方法及若干准则

我希望,我能帮助我!

我有一个这样的数据框:

        A       B       C       D       E       F     G
1       4       2       3       0       0       0    (Here the formula)         
2       0       0       0       0       0       0    (Here the formula)
3       3       2       4       5       0       0    (Here the formula)
4       0       0       0       0       5       2    (Here the formula)
5       0       0       0       5       5       5    (Here the formula)

在G列中,计数公式必须符合以下条件:with<>:distinct of

我已经计算了总数(如果(a2<>0和a3<>0)+(b2和b3)……

例如:我试着做这个;

在G1中

=SUM(COUNT(IF(AND(A1<>0;A2<>0);1;0);COUNT(IF(AND(B1<>0;B2<>0);1;0);COUNT(IF(AND(C1<>0;C2<>0);1;0).....COUNT(IF(AND(F1<>0;F2<>0);1;0);COUNT(IF(AND(A1<>0;A3<>0);1;0);COUNT(IF(AND(B1<>0;B3<>0);1;0);COUNT(IF(AND(C1<>0;C3<>0);1;0).....COUNT(IF(AND(F1<>0;F3<>0);1;0);............
COUNT(IF(AND(A1<>0;A5<>0);1;0);COUNT(IF(AND(B1<>0;B5<>0);1;0);COUNT(IF(AND(C1<>0;C5<>0);1;0).....COUNT(IF(AND(F1<>0;F5<>0);1;0))

对于每个G2、G3、…G5

也就是说,我只想对同一列中只有非零值的单元格进行计数,然后将它们相加,如下所示:

G1 is equal to:
       
= (0 + 3 + 0 + 0)

G2 is equal to:
       
= (0 + 0 + 0 + 0)

G3 is equal to:
       
= (3 + 0 + 0 + 1)

G4 is equal to:
       
= (0 + 0 + 0 + 0)

G4 is equal to:
       
= (0 + 0 + 1 + 2)