Ask Your Question

character "0" wrong count

asked 2015-02-20 19:35:51 +0200

Jau gravatar image


I'm trying to count the "0" characters in a column. In this column the formula can answer "0", "1" or "2". The are some merged cells between some of the regular ones. They are filed with text (no "0" characters here)

Here is my formula : =NB.SI(K9:K186;"0")


It counts the fused ones as "0". I verified, there are 102 "0" cells but it says 107, as if the fused title cells between the other ones contained "0".

Why ?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2015-02-20 21:34:32 +0200

Lupp gravatar image

updated 2015-02-20 21:37:17 +0200

I did not clearly understand, and I do not know the language of your function names. I suppose NB.SI() is what in English shows as COUNTIF().

In this case: COUNTIF(K9:K186;"0") will count those of the 178 cells in the range which contain (exactly) the text "0". If you get the result 107 while you only see 102 times the "0", this should mean that in 5 cases the hidden background cell of a cell which is displayed as merged is also containing a "0" text. Split all these cells (Select the complete column best) and you will see.

Please note: On merging cells you are prompted "Should the contents of the hidden cells be moved into the first cell?" If you reject this there will exist a hidden cell behind the "merged" one, still containing the old content which can be a formula. The hidden cell can be referenced and will be evaluated by respective formulae.

edit flag offensive delete link more

answered 2015-02-21 02:38:52 +0200

Jau gravatar image

Thank you so much !

Sry for the NB it's in french version ^^"

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-02-20 19:35:51 +0200

Seen: 38 times

Last updated: Feb 21 '15