组合SUMIFS或SUMIF

Question

这是我的数据：https://docs.google.com/spreadsheets/d/1PX7-RxYJGi74bwUZ8Y2QzJEkKDIdTRL6yjQvWYcUxm0/edit#gid=959894984

A       B       C           D       E                F           G
date    item    warehouse   qty     last checked                 a
1       a       x           1       4                            b
4       a       x           5       4 
8       a       x           10      4 
5       a       x           7       4 
7       a       y           2       5 
5       a       y           3       5 
5       b       x           1       5 
6       b       y           2       6 
7       b       x           4       6 
8       b       y           5       6 

我想做如下计算：

=SUMIFS(D:D,B:B,"b",C:C,"x",A:A,">"&INDEX(E:E,MATCH(1,("b"=B:B)*("x"=C:C),0))) + SUMIFS(D:D,B:B,"b",C:C,"y",A:A,">"&INDEX(E:E,MATCH(1,("b"=B:B)*("y"=C:C),0)))

有没有办法让我这样做：

=SUMIFS(D:D,B:B,"b",C:C,"x",A:A,">"&INDEX(E:E,MATCH(1,(("a" or "b"=B:B)*("x"=C:C),0))) 

或

=SUMIFS(D:D,B:B,"b",C:C,"x",A:A,">"&INDEX(E:E,MATCH(1,({a,b}=B:B)*("x"=C:C),0)))

或者给定"a“和"b”位于G中：

=SUMIFS(D:D,B:B,"b",C:C,"x",A:A,">"&INDEX(E:E,MATCH(1,(g:g=B:B)*("x"=C:C),0))) 

Answer 1

执行此("a" or "b"=B:B)*("x"=C:C)的有效方法是：

(("a"=B:B)+("b"=B:B))*("x"=C:C)

更新：

=SUMIFS(D:D,B:B,"b",C:C,"x",A:A,">"&INDEX(E:E,MATCH(1,(("a"=B:B)+("b"=B:B))*("x"=C:C),0)))

​

​