Counting occurrences of a value, two columns

Question

I want to count the occurrences of values >=13.8 in two columns, no problem with that - but if both of the values on the same row is >=13.8 I want it to count as one match.

Here's an example

The result should be 6

I know I can add a third column with the result for each row using a simple formula and that count values in that column. But its quite a massive document and a lot of sheets, so if it's possible to do the calculation in one formula that would have been great.. :)

Any ideas?

Answer 1

If I understood your question right, you can reformulate the task to: Count any row where one of the two columns (or both) contains a value >=13.8.

You could use SUMPRODUCT to achieve your goal (suppose the data you've given is in cells B1 to C7):

=SUMPRODUCT( ( ( ( B1:B7 >= 13.8 ) + ( C1:C7 >= 13.8 ) ) > 0 ) * 1 )

What it does is evaluate the condition for each of the arrays (so we get an array of true and false for each row) and then add their values. So you get an array of 0s, 1s, and 2s (according to whether no cell in the current row had a matching value, one cell or both). These values are then checked (by the > 0) and you get an array of true and false again which we multiply with 1 to be able to get a sum.

You can follow the formula execution by choosing Formulas > Formula Auditing > Evaluate Formula from the ribbon (detailed explanation with screenshots here).

Answer 2

You can continue to use COUNTIFS here, viz:

=SUM(COUNTIFS(B:B;{">=",">=","<"}&13,8;C:C;{"<",">=",">="}&13,8))

which, although arguably a touch convoluted, does have the advantage over SUMPRODUCT that you can reference entire columns without detriment to performance.

Of course, this set-up is not easily extendable to handle more criteria. However, perhaps worth considering in this case.

Note that you may need to amend the separators in the array constants so that they are of a valid format for your system, e.g.:

{">="\">="\"<"}

or perhaps:

{">=".">="."<"}

Regards

Answer 3

I got this one for you:

=SUM( COUNTIF( A:A;">=13,8" ) ; COUNTIF( B:B;">=13,8" ) ) 
 - COUNTIFS( A:A;">=13,8" ; B:B;">=13,8" )

It counts the cells meeting your condition in each column and subtracts the number of rows where both columns meet the condition.