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

$C$1:$G$15

on the

MMULT(--($C$1:$G$15<>"");{1;1;1;1;1})

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..