A conditional counting issue

Hello everybody.

Here is an issue I am dealing with:

Column H is the key column, indicating how many "answers" can be found at each row of the yellow range. In the column I we want to count how many rows on the yellow range have 0 answers (which are the last five), how many rows on the yellow range have 1 answer (which is the first one), etc.

These can be achieved by the formulas on column I.

Now, how could this be done if we wanted the range

Please Login or Register  to view this content.

on the

Please Login or Register  to view this content.

part of the formula be consisted only by those rows of the yellow area that have either 1 or 4 on column A? (i.e. rows 5,8,9,11)

And another question:

Is the <>"" part of the formula safe enough for this purpose, considering that we want a TRUE returned only for the cells of the range containing visible characters? (i.e. if a cell, say C11, doesn't contain a visible character and the result of C11<>"" is TRUE, then we have a bad situation).

If it is not considered to be safe, do we have any alternatives?

Originally Posted by dilipandey

why not rows 1 and 2 ?

Sorry for this. Of course the lines are 1,2,5,8,9,11,12.

Originally Posted by dilipandey

<>"" says does not equal to blank so.. YES

The part of my question regarding <>"" stemed from two things:

1) While I was experimenting with this issue, when I was evaluating the formula, I noticed that from a range of cells that contained no visible content, some of them were seen as "" and some of them as 0. Now this caused trouble because after the <>"" step, the cells that were seen as "" returned False but the cells that were seen as 0 returned True, while I wanted them to return False as well.

2) I would like True to be returned from the <>"" step, only by the cells that contain visible content. This thing won't happen if a cell contains a linefeed for example or an &nbsp;

Any ideas how these can be settled?

Thank you very much

When I double click on C11 on the spreadsheet I uploaded and hit Alt+Enter and then Enter, I1 from 5 changes to 4 and I2 from 1 turns to 2 as expected. Doesn't this happen when you do it also?

The thing is that there is a bunch of non printable characters that can be treated with Trim-Clean formula and another bunch that cannot be treated by those formulas. So how do we tackle this situation.. Non printable characters will give wrong counting results..