CODEWITHSUNDEEP
google-sheetsfiltergoogle-sheets-formulaarray-formulasgoogle-sheets-query
Dushy
Dushy

Reputation: 37

Can't find the formula to count columns with multiple criterias (OR and AND)

Image

I'm trying to figure out the way to count the number of Draw for each team. To count as a draw, a match had to be played (So no empty score value on both cells, and the team must appear in either column B or E, and the result must be "Draw")

Can anyone help me find the proper formulae? I already tried to use COUNTIFS but couldn't succeed.

Upvotes: 1

Views: 44

Answers (2)

player0
player0

Reputation: 1

=QUERY(FILTER({B2:B; E2:E}, {F2:F; F2:F}="Draw"), 
 "select Col1,count(Col1) group by Col1 label count(Col1)''", 0)

0

and F2 cell would be:

=ARRAYFORMULA(IF(E2:E<>"", 
 IF(C2:C=D2:D, "Draw", 
 IF(C2:C>D2:D, B2:B, 
 IF(C2:C<D2:D, E2:E, ))), ))

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Use Two COUNTIFS() and add them together:

=COUNTIFS(B:B,"Real Madrid",F:F,"Draw")+COUNTIFS(E:E,"Real Madrid",F:F,"Draw")

Upvotes: 0

Related Questions