4

Consider the following sample data.

A B
1 2
8 4
2 1
3 7
4 8

Separately, the count of A that is less than 5 is =COUNTIF(A1:A5, "<5") -> 4, and that for B is =COUNTIF(B1:B5, "<5") -> 3.

I know if I want the count of rows where A and B are < 5, I can use COUNTIFS: =COUNTIFS(A1:A5, "<5", B1:B5, "<5") -> 2 because COUNTIFS evaluates conditions row-by-row. However, I can't figure out an elegant way to get the count of rows where A or B is < 5.

Using the sample data, the answer should be 5. So far I've tried:

  • =COUNTIF(A1:A5, "<5") + COUNTIF(B1:B5, "<5") -> 7, because the cells aren't treated as rows. This just counts the number of cells <5.
  • =SUM(IF(A1:A5 < 5, 1, 0), IF(B1:B5 < 5, 1, 0)) -> 7, does the same thing as the above.
  • =SUM(IF(OR(A1:A5 < 5, B1:B5 < 5), 1, 0)) -> 1, because the cells are not treated as rows, and the OR returns just one value combining the results of each cell condition. Evaluates to 1 if any cell is <5, 0 if none are.

I have found one functioning solution which uses the addition theorem of probability (see my answer for that), but I'd like a way to do this that's easier to read and scale, if possible. How can I achieve this?

Drake
  • 105

4 Answers4

6

In Excel 365 you can use FILTER function:

=COUNT(FILTER(A1:A5,(A1:A5<5) + (B1:B5<5)))

separating conditions by * will act as AND, while separating them by + will work as OR.

Máté Juhász
  • 22,071
5

Mathematically x OR y is equivalent to x + y >= 1 with x and y being boolean values. And in Excel to convert from bool to int we typically use --. So the either of the below will work

=SUM(--(IF(A1:A5 < 5, 1, 0) + IF(B1:B5 < 5, 1, 0) > 0))
=SUM(--(--(A1:A5 < 5) + --(B1:B5 < 5) > 0))

It can even be shortened to

=SUM(--(-(A1:A5 < 5) + -(B1:B5 < 5) < 0))
=SUM(--(-(A1:A5 < 5) - (B1:B5 < 5) < 0))
phuclv
  • 30,396
  • 15
  • 136
  • 260
4

Not the prettiest solution and it gets very messy when you have more than two conditions, but one can use the addition theorem of probability P(A or B) = P(A) + P(B) - P(A and B) to calculate the requested count:

  • =COUNTIF(A1:A5, "<5") + COUNTIF(B1:B5, "<5") - COUNTIFS(A1:A5, "<5", B1:B5, "<5") -> 5
Drake
  • 105
2

If the columns in question are contiguous, then a more flexible and scalable solution is:

=SUMPRODUCT(N(MMULT(N(A1:B5<5),TRANSPOSE(COLUMN(A1:B5)))>0))

Or, for O365:

=SUM(N(MMULT(N(A1:B5<5),SEQUENCE(COLUMNS(A1:B5)))>0))
phuclv
  • 30,396
  • 15
  • 136
  • 260
Jos Woolley
  • 541