The COUNTIF can only have one criterion:
COUNTIF(range, criterion)
When I was customizing summary of a forms 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 its not yet available universally, you have to enable the feature, even so its 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)))
Its fairly quite straightforward, its 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