Question

EXCEL公式问题？

14号的数据，如图应该用什么公式求出来当日成本，





Answer 1

E3输入：

=SUMIFS(Sheet1!C:C,Sheet1!A:A,D$2,Sheet1!B:B,D3)

回车并向下填充。

Answer 2

假设第2图在 Sheet2
E3=SUMPRODUCT((Sheet2!$A$2:$A$500=$D$2)*(Sheet2!$B$2:$B$500=$D3)*(Sheet2!$C$2:$C$500))
下拉公式
F3=SUMPRODUCT((Sheet2!$A$2:$A$500=$D$2-1)*(Sheet2!$B$2:$B$500=$D3)*(Sheet2!$C$2:$C$500))
G3=SUMPRODUCT((Sheet2!$A$2:$A$500>=($D$2-7))*(Sheet2!$A$2:$A$500<=$D$2)*(Sheet2!$B$2:$B$500=$D3)*(Sheet2!$C$2:$C$500))
都下拉公式
-7就表示上周

Answer 3

E3=sumifs(sheet2!c:c,sheet2!a:a,$d$2,sheet2!b:b,d3)
f3=sumifs(sheet2!c:c,sheet2!a:a,$d$2-1,sheet2!b:b,d3)
g3=sumifs(sheet2!c:c,sheet2!a:a,$d$2-7,sheet2!b:b,d3)
三个公式向下拉自动填充。

Answer 4

很简单，这个应该是使用数据透视图就可以了

Answer 5

看不到你的行号，姑且当你河北单元格为D3
在E3单元格写入公式
当日成本=SUMIFS($C$2:$C$19,$B$2:$B$19,$D3,$A$2:$A$19,$D$2)
在F3单元格写入公式
前日成本=SUMIFS($C$2:$C$19,$B$2:$B$19,$D3,$A$2:$A$19,$D$2-1)
在G3单元格写入公式
上周当日成本=SUMIFS($C$2:$C$19,$B$2:$B$19,$D3,$A$2:$A$19,$D$2-7）