The COUNTIF can only have one criterion:
COUNTIF(range, criterion)
When I was customizing summary of a form’s responses, I realized that I needed the new COUNTIFS:
COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
I learned about this new addition, but also realized that it’s not yet available universally, you have to enable the feature, even so it’s still not in the sheet I was editing after I turned it on.
So I could only do it in old fashion way with ARRAYFORMULA and SUM. For example, considering the following sheet:
Name | Number |
---|---|
A | 5 |
B | 2 |
A | 1 |
A | 3 |
C | 4 |
B | 0 |
The data range is A2:B7, two-column. Each column has its own criterion, first one is name with A and second one is the number has to be at least 3. If you want to do with COUNTIFS, that would be:
=COUNTIFS(A2:A7, "=A", B2:B7, ">=3")
The results would be in this case, two rows meet the criteria, one is A 5 and another is A 3.
With ARRAYFORMULA, then it is:
=ARRAYFORMULA(SUM((A2:A7 = "A") * (B2:B7 >= 3)))
It’s fairly quite straightforward, it’s like matrix dot operation, the two criteria evaluate the data into true (1) or false (0). Only cells with both are evaluated as true would result in numeric value as SUM tallying up.
You can even use like having a weighting function, e.g.:
=ARRAYFORMULA(SUM((A2:A7 = "A") * (B2:B7 >= 3) * B2:B7))
It would be . I just use same values from second column to weight the result.
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.