# SUMPRODUCT的替代品

TALLY SHEET具有以下列（按顺序）：日期和总持续时间。

``````=SUMPRODUCT(SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1),
SUMIFS('RAW DATA'!C:C,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1))
``````

robinCTS

`SUMPRODUCT(SUMIFS(«Total»),SUMIFS(«Duration»)/SUMIFS(«Total»)`与相同，当然与`SUMIFS(«Total»)*SUMIFS(«Duration»)/SUMIFS(«Total»)``SUMIFS(«Duration»)`

``````=SUMPRODUCT('RAW DATA'!\$B\$2:\$B\$10*('RAW DATA'!\$A\$2:\$A\$10=A2)*('RAW DATA'!\$D\$2:\$D\$10=1),
'RAW DATA'!\$C\$2:\$C\$10*('RAW DATA'!\$A\$2:\$A\$10=A2)*('RAW DATA'!\$D\$2:\$D\$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)
``````

`('RAW DATA'!\$A\$2:\$A\$10=A2)`部件检查日期是否匹配，并且`('RAW DATA'!\$D\$2:\$D\$10=1)`部件检查计数器是否相等`1`

`SUMPRODUCT()`功能交叉相乘的两个阵列，并然后对其求和。

``````=SUMPRODUCT('RAW DATA'!\$B\$2:\$B\$10*'RAW DATA'!\$C\$2:\$C\$10*('RAW DATA'!\$A\$2:\$A\$10=A2)*('RAW DATA'!\$D\$2:\$D\$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)
``````

``````=SUMPRODUCT(("0"&'RAW DATA'!\$B\$2:\$B\$10)*("0"&'RAW DATA'!\$C\$2:\$C\$10)*('RAW DATA'!\$A\$2:\$A\$10=A2)*('RAW DATA'!\$D\$2:\$D\$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)
``````

``````{=SUM(IF(('RAW DATA'!\$A\$2:\$A\$10=A2)*('RAW DATA'!\$D\$2:\$D\$10=1),'RAW DATA'!\$B\$2:\$B\$10*'RAW DATA'!\$C\$2:\$C\$10,0))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)}
``````

``````=SUM(IF(('RAW DATA'!\$A\$2:\$A\$10=A2)*('RAW DATA'!\$D\$2:\$D\$10=1),("0"&'RAW DATA'!\$B\$2:\$B\$10)*("0"&'RAW DATA'!\$C\$2:\$C\$10),0))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)
``````

0条评论