Google Sheets Countif with Arrrayformula

Question

I'm doing some dynamic Monte Carlo simulation in Google Sheets, by utilizing the COUNTIF formula for the simulation. Something is not working the way I thought it would, but I cannot put my finger on. I have two columns that I'm comparing, and I need to count the instances where the value in one column is bigger than the value in the other column. If I do this explicitly by propagating the if comparison formula I obtain the correct result. However, if I do it with

=countif( A4:A, ">" & B4:B )

I do not obtain the correct result. My example is at this sheet, the number in cell C4 is the malfunctioning COUNTIF, which equals 2 in the example, and the number in cell E4 is 5, which is the correct count by propagating the comparison in column F and adding the correct comparisons in E4.

p1  p2  n           
0.5 0.51    10          
Monte Carlo                 
0.50    0.60    2       5   0
0.90    0.50                1
0.60    0.30                1
0.50    0.60                0
0.40    0.30                1
0.40    0.50                0
0.60    0.70                0
0.60    0.30                1
0.70    0.50                1
0.10    0.30                0

Tom Sharpe Tom Sharpe · Accepted Answer · 2020-05-01T08:59:37

There are two scenarios with countif:

(1) As a non-array formula, =countif( A4:A, ">" & B4:B ) would give you the same result as =countif( A4:A, ">" & B4 ) i.e. it would count only values of A greater than .60, giving the answer 2.

(2) As an array formula, =sum(countif( A4:A, ">" & B4:B )) would give you a separate result for each value of B (2+5+9+2...) giving the answer 56.

If you wanted to use countif, you would need to do something like this:

=ArrayFormula(countif(A4:A-B4:B,">"&0))

Google Sheets Countif with Arrrayformula

2 Answers