CODEWITHSUNDEEP
arraysexcel
Joseph Williams
Joseph Williams

Reputation: 25

Count(If) Formula not returning correct result in Array

I had a standard COUNTIF that counts specific amounts of data in another workbook. 

=COUNTIF('[Cumbria Jan.xlsx]Details '!$O$3:$O$5900,50%)

This returns the correct value of 64.

However, to get this to work if the other workbook is closed, I've transformed the formula into an array and it now looks like this;

{=COUNT(IF('[Cumbria Jan.xlsx]Details '!$O$3:$O$5900,50%))}

This however returns an incorrect result of 76.

Can someone explain to me what's going on here please?

Upvotes: 2

Views: 69

Answers (1)

Jerry
Jerry

Reputation: 71538

That's simply because the transformed formula is wrong. You are basically saying "If $O$3:$O$5900 (any of these cells are not blank or zero or 'false'), then return '50%', then count the number of 50% returned".

I think you probably wanted to use something like this:

=COUNT(IF('[Cumbria Jan.xlsx]Details '!$O$3:$O$5900=50%,1))

"If $O$3:$O$5900 = 50%, then return 1, then count the number of 1 returned" (for which you could also use SUM instead of COUNT now for the same results).

Upvotes: 2

Related Questions