Combining SUMIFS or SUMIF

1
votes

this is my data: 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

i want to do a calculation as below: 

=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)))

is there any way for me to make it like this:

=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)))

or

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

or given that "a" and "b" is located in G: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)))
1
google-sheetsgoogle-sheets-formulasumifs
sheet is private – player0
docs.google.com/spreadsheets/d/… this is the updated link, thankyou!! – Randy Adikara
answer updated... – player0

1 Answers

0
votes

valid way of doing this ("a" or "b"=B:B)*("x"=C:C) would be:

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

UPDATE:

=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)))

0