Excel 2010 CountIf function only works with debug

0
votes

I have the following excel function:

=SUM(COUNTIF($B22:$BA22,$BY$4:$BY$49))

The first range B22 - BA22 is filled with formulas that read data from other spreadsheets.

The values displayed in those cells are like "1234Pass", "3456Fail".

The values in the second range, BY4 - BY49, contain all of the possible Fail values. Those are typed in manually. All Cells are formatted as general.

The SUM formula above always displays 0 in the cell. However if I click in the formula bar and press F9, the correct value is displayed.

What am I doing wrong?

1
excelcountif
check if Application.Calculation = xlCalculationAutomatic from immediate window. Take a look at this: msdn.microsoft.com/en-us/library/office/… – Horaciux
Enter the formula as an array formula -- hold down ctrl+shift while you hit enter. Excel will put braces {...} around the formula if you do it correctly. – Ron Rosenfeld
@pnuts No Problem. Done. – Ron Rosenfeld

1 Answers

1
votes

Enter the formula as an array formula -- hold down ctrl+shift while you hit enter. Excel will put braces {...} around the formula if you do it correctly