Calc: Apply COUNTIF to just the last few non-empty cells?

English
#1

Hello everyone! I am looking for a solution to a problem that came up while maintaining a spreadsheet and that I just can’t seem to fix.

The post title pretty much says it all. I would like to apply the COUNTIF function to nothing but the last 20 used cells in a column.

For context: Each column in the spreadsheet contains data for one person. There are only two types of cells; some contain “0” and others contain any other number. There is no text involved and there are no blank cells (except for the first few lines). The COUNTIF function should give me the numbers of cells with values “0” and “else” respectively for each column. For a fixed range or the whole column, this is trivial.

What I want, however, is just the last 20 cells. As there is still data being added, I can’t just select a fixed range and call it a day; I need a function that does this automatically.

I have already exerimented with combinations of INDIRECT, CONCATENATE and COUNTIF but to no avail. Does anyone have an idea how to implement this?

#12

PowerFilter.odb (155.0 KB)
Open the attached database document and the embedded form document “Last 20”.

#2

t72450.ods (14.1 KB)

#3

Thank you for the suggestion, however this doesn’t actually solve the problem I have. Simply defining the lowermost cell for the ROWS function is exactly what I can’t do as there is still data being added below whatever the lowest cell currently is. I would have to redefine the lowest cell for every new entry I make, which is what I was trying to avoid in the first place.

#8

Any spreasdheet application (Excel, Calc, Gnumeric) expands references by insertion of cells. Insertion of cells updates all references in formulas, conditional formats, named ranges, charts, validation ranges and elsewhere.
Having a reference to more than one row and Tools>Options>Calc>General>"Expand references… " being checked, any insertion within A1:A38 or directly adjacent below A38 will expand all references properly across the entire spreadsheet document.

When Tools>Options>Calc>General>"Expand references… " is not checked (unfortunately this is the default), any insertion directly below will not expand references and any insertion on top of the will shift down the reference.

#4

Adding data rows at the bottom comes with lots of complications, if recent data are to be treated in a special way… Regarding the needs (including to freeze header rows e.g, but many more) it can be done, however.
If we accept that a spreadsheet isn’t a sheet of paper, we can overcome the built in top-down evaluation, and avoid some complications.
The attached example is based on what @Villeroy provided,. but also demonstrates a flexible but “expensive” enhancement for adfdiung rows below, and a solution where new rows are to insert on top.
t72450_SheetsAddedByLupp.ods (35.3 KB)

#5

We can also do this in @Villeroy example provided that there are no blank cells in column A (in used range).

=COUNTIF(OFFSET($A$1;1000-COUNTBLANK($A$1:$A$1000)-$D$1;;$D$1);$D$2)

Instead of 1000, you can take any number that is large enough.

1 Like
#6

What’s large enough?
The third sheet is demontrating a solution not needing any such “hard coded” value.
(I see questions again and again where a questioner asks for a way to find/match/evaluate something against the predefined order. If not both directions are needed alternately the concept of inverting the order of data from the beginning should simplify things.)

#7

Any constant that obviously exceeds the possible number of filled cells. This is usually known from context.

#9

Yes. That’s a very strange design anyway, but…

… makes me think there are 20 rows added per day (For some persons? For all of them?), and this gives up to 584400 data rows in only 80 years.

(I will leave thjis thread now.)

#11

Thank you very much, this is exacly what I was looking for!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.