excel - Excel SUMPRODUCT/COUNTIFS #DIV/0 错误

Question

我有数据，我试图求和charge（全部）并将其除以num_persons但只有 SUM的 SUMnum_persons时，另一列res_num尚未出现date。

这是我的数据（我已将重复的 res 编号更改为更容易找出数字）：

     A         B        C         D          E
1    acct_mgr  charge   res_num   date       num_persons
2    johnd     170      62570     6/1/2018   1
3    johnd     140      74001     6/1/2018   1
4    johnd     140      74003     6/1/2018   1
5    johnd     135      74006     6/1/2018   1
6    johnd     195      74008     6/1/2018   1
7    johnd     140      74019     6/1/2018   1
8    johnd     75       74021     6/1/2018   1
9    johnd     140      74027     6/1/2018   1
10   johnd     140      74032     6/1/2018   1
11   johnd     135      74657     6/1/2018   1
12   johnd     85       74662     6/1/2018   1
13   johnd     140      74665     6/1/2018   1
14   johnd     75       11111     6/1/2018   2
15   johnd     75       11111     6/1/2018   2
16   johnd     135      77134     6/1/2018   1
17   johnd     140      77198     6/1/2018   1
18   johnd     75       79546     6/1/2018   1
19   johnd     100      22222     6/1/2018   1
20   johnd     135      22222     6/1/2018   1
21   johnd     135      33333     6/1/2018   1
22   johnd     140      33333     6/1/2018   1
23   johnd     100      44444     6/1/2018   2
24   johnd     140      44444     6/1/2018   2
25   johnd     140      80895     6/1/2018   1
26   johnd     140      81327     6/1/2018   1
27   johnd     140      82025     6/1/2018   1
28   johnd     75       83577     6/1/2018   1
29   johnd     140      83749     6/1/2018   1
30   johnd     170      83814     6/1/2018   1
31   johnd     170      83817     6/1/2018   1
32   johnd     75       55555     6/1/2018   2
33   johnd     140      55555     6/1/2018   2
34   johnd     140      51008     6/2/2018   1
...

因此，对于这个示例数据，6/1/2018-charge是 4075 并且num_persons是 30，这是我的答案135.83。

如果我使用给定的公式 ( =SUMPRODUCT(E2:E33/COUNTIFS(A2:A33,H2,D2:D33,H3,E2:E33,E2:E33,C2:C33,C2:C33))) 它可以正常工作。问题是当我将所有的更改33为34，因此输入一个新日期时，它给出了#DIV/0!.

请注意，在真实数据中不止一个acct_mgr。

标签： excelexcel-formula

Answer 1

利用：

=SUMPRODUCT((E2:E34*(A2:A34=H2)*(D2:D34=H3))/(COUNTIFS(A:A,H2,D:D,H3,E:E,E2:E34,C:C,C2:C34)+(A2:A34<>H2)+(D2:D34<>H3)))