How do I stop counting using CountA when it reaches a blank cell? (without editing my data input)

Abby,

rather than ask for how to make an excel formula work in Numbers try telling us the result you want an we can provide how to achieve that.

Also,please post sample data (no personal or confidential information please) here so we have representative data to work with.

So, again, show us data and tell us what you want, then we can figure out how.

Hi Abby,

I am not sure how your table is arranged. Please post a screen shot of an imaginary table (or copy some cells including any Header and Footer rows, then paste into a reply).

I am thinking that somehow the ROW function will feature in a solution.

repeatedly inputting "diabetes" or "hypertension" in each row is visually annoying.

I agree. But you can hide rows and columns, or filter a table.

More information will lead to a solution 🙂.

Regards,

Ian.

abby_md09 wrote:

How do i count all the non-empty rows after my formula until it reaches the first blank row?

If you don't mind inserting a space where you have blanks in that column (e.g. here a space in B5 and B11) thenyou could do something like this:

=MATCH(" ",B2:B11,0)−1

Provided the range given to the MATCH function stretches from the cell immediately below the formula to the end of the table (here B2:B11) you can then copy the formula from B1 and paste into B6 and so on down your table.

SG

abby_md09 wrote:

It would only work if I put a space in the empty cell. Thank you, hope you can still help me.

That's why I wrote:

If you don't mind inserting a space where you have blanks in that column (e.g. here a space in B5 and B11)

It seems you do mind inserting blanks, which have the virtue of being invisible and not marring your presentation.🙂

Normally I find life much easier if I put the source data in a table format and extract reports from it for summary and presentation. But doesn't appear to meet your needs either.

It's difficult for us to understand the constraints you are operating under without more specifics.

SG

The I would have suggested using two tables. This accomplishes (without knowing any of your other considerations) smaller data sets and simplifies the formula:

Make the first two rows header rows.

Double click the title of the table (probably Table 1, or something like that) and change the name to "Hypertension")

B1="Entries = "&COUNTA(B)

this is shorthand for... select cell B1, then type (or copy and paste from here) the formula:

="Entries = "&COUNTA(B)

Now enter names and Descriptions (or what ever)

now select the table, and use the menu item "Edit > Duplicate" to duplicate the table

. Change the name to "Diabetes"

and update the details

Thought of another variation. If you don't want to insert spaces in various cells in a column and you want to avoid extra tables then you could add an auxiliary column (which you can then easily hide) containing one consistent formula copied down from row 2 to all its cells below:.

In C2, copied down:

=IF(A2="",0,C1+1)

The formula in B2, copied to B7, etc. is:

=MATCH(0,C2:C12,0)−2

Copied to B7 that automatically becomes:

=MATCH(0,C7:C12,0)−2

In practice you wouldn't have C12 but instead the last cell in the column.

This approach requires a blank row above your data and below it.

SG

If your COUNTA function includes the blank cell at the bottom of the list you are counting, when you add a new row for a new item, it will include the new row in the formula. Using SGIII's data, the first COUNTA would be COUNTA(B2:B5). You can then adda new row below row 4 or above row 5 and the formula will expand to include it.

Hello

Numbers does not support most of the array formulae that Excel does.

An option is to use an auxiliary column to check the blankness, which you may hide. Something like this:

Table 1 A1 A2 Hypertension A3 Jane Doe A4 John Doe A5 Jack Smith A6 A7 Diabetes A8 Jane Lee A9 Jenny Ko A10 Judy Do A11 B1 B2 =MATCH(TRUE,OFFSET(C$1,ROW()-1,0,ROWS(C)-ROW()+1,1),0)-2 B3 Diagnosis of Jane Doe, Gestational Hypertension B4 Diagnosis of John Doe, Chronic Hypertension B5 Diagnosis of Jack smith, Secondary Hypertension B6 B7 =MATCH(TRUE,OFFSET(C$1,ROW()-1,0,ROWS(C)-ROW()+1,1),0)-2 B8 GDM Diet Controlled B9 Gestational Diabetes B10 GDM Insulin B11 C1 blank? C2 =A2="" C3 =A3="" C4 =A4="" C5 =A5="" C6 =A6="" C7 =A7="" C8 =A8="" C9 =A9="" C10 =A10="" C11 =A11=""

Another option is to put explicit empty string by formula in delimiter cell and use match() for it. Something like this:

Table 1 A1 A2 Hypertension A3 Jane Doe A4 John Doe A5 Jack Smith A6 ="" A7 Diabetes A8 Jane Lee A9 Jenny Ko A10 Judy Do A11 ="" B1 B2 =MATCH("",OFFSET(A$1,ROW()-1,0,ROWS(A)-ROW()+1,1),0)-2 B3 Diagnosis of Jane Doe, Gestational Hypertension B4 Diagnosis of John Doe, Chronic Hypertension B5 Diagnosis of Jack smith, Secondary Hypertension B6 B7 =MATCH("",OFFSET(A$1,ROW()-1,0,ROWS(A)-ROW()+1,1),0)-2 B8 GDM Diet Controlled B9 Gestational Diabetes B10 GDM Insulin B11

Tables are built with Numbers v2.

Regards,

H

Hi H,

Just a heads up that in my testing your formula as posted =MATCH("",OFFSET(A$1,ROW()-1,0,ROWS(A)-ROW()+1,1),0)-2 does not seem to work either in Numbers 3 or Numbers 2 (or in Excel 2016).

In Numbers 3:

In Numbers 2:

From my testing the formula will work with a space between the "" instead of NULL and a space in the "delimiter" cells. But then, so will the simpler, non-volatile (and thus less likely to cause sluggish performance than OFFSET) formula I posted upthread:

=MATCH(" ",B2:B11,0)−1

As far as I can tell it is possible to match on " " but it is not possible to match on "". Is there something you are doing that isn't that easy to see from your screenshot?

SG

Thanks for the green tick!

Here's a trick for the future. Suppose you have data in a "database" table format, say something like this:

The counts, averages, sums then become very easy, using COUNTIF, AVERAGEIF, SUMIF.

The drawback is, as you say, the visually annoying repetition of Hypertension, Diabetes, etc.

But Conditional Highlighting provides a quick and easy remedy for that.

Select the cells, go to Conditional Highlighting and click the symbol in the box that looks like a cell with an arrow pointing to it:

Click in cell A1:

Then change the Bold to Custom Style (last choice after scrolling down) and pick white or whatever matches your background:

The result, before further prettying up, is something like this:

The values are still there in the now "hidden" cells. So your formulas can still automatically calculate summaries from the data, and you can easily add new data that will automatically update the summary table. But the data table can now be made suitable for presentation.

And, of course, you can base visually appealing charts on the summary table that automatically draws its information from the data.

SG