Hello! I have a spreadsheet with much information about my school. Specifically speaking, the names of the students occupy a column of this spreadsheet, while at the same time the other columns carry information about each student, including the elective lessons he/she has chosen to attend (for example History, Sociology, e.t.c.). Each student belongs to a group, and each group belongs to a class (year) of my school. I need to make statistics for each group, each class (year) and the whole school, for counting how many students have chosen each one of the elective lessons. It would be really useful if I could use COUNTIF by defining the “range” of cells of each group DYNAMICALLY. I mean that instead of defining directly the range of cells for COUNTIF, it would be more effective and efficient if the first and last of this “range” could be “variables” which could be stored in other cells. For example, instead of defining directly the range for the group A1 as D5:D30, I need to store the information “D5” and “D30” in other cells (for example Z1 and Z2 respectively), and address the COUNTIF to these cells (Z1 and Z2). This is highly useful, since each year the range of cells for each group changes, and this approach needs just to change the values of the cells Z1 and Z2. Is it possible to do it? How? Thanks a lot!
Of course it is possible! The best and easiest way is to use Base, not Calc. Don’t be angry, this is just a joke. But in every joke there is only a fraction of a joke - everything else is true.
Thank you both for your answer and your joke! 
Please try INDIRECT() function
=COUNTIF(INDIRECT(Z1&":"&Z2);"<your condition>")
Then add the OFFSET() function to shift your attention to the adjacent range of the same size
=COUNTIF(OFFSET(INDIRECT(Z1&":"&Z2);0;1);"<your condition>")Hello JohnSUN! The other columns of my spreadsheet carry other types of information. For example, while column D carries the information of the elective lessons, column E (let’s say cells E5 to E30 for group A1) might carry the information of gender (male, female) for each student. So, if I need to make statistics for the students’ gender, I need to refer indirectly to the cells E5 and E30, which means: I need to use IDENTICAL rows with the cells D5 and D50, but different COLUMNS. I was thinking of adding this information (corresponding columns for example, D, E, etc) to some specific cells and the “range” 5 to 30 (for the group A1) to different cells. Is it possible for INDIRECT to read (indirectly) both the values of the column (for example D, which is entered in the cell Z1) and the values of the number of the row (for example read indirectly the values 5, 30, which are entered in the cells X1 and W1 respectively) and combine them? (Am I too lazy???) 
INDIRECT simply interprets the text passed to it on the single parameter position as a text-representation of a reference (often called an address) and returns the functional reference itself. The mentioned address you can calculate [compose by CONCATENATE() / & operator / SUBSTITUTE() / LEFT() or whatever means] in any way you want. INDIRECT will not know how you did it. It only gets passed the result.
Thank you for your answer!
In addition to the suggestions by @JohnSUN:
There is also the function OFFSET() capable of returning a cell- or a range-reference calculated from parameter values. To address cells or cell ranges in text-form there is the ADDRESS() function. The function INDEX() helps to extract specific parts from an array (data from a rectangular range). It supports single_array-row, single_array-column, and single_array-element.
All the mentioned means, at least if used with variables instead of constants only on parameter positions share a serious disadvantage: The routines of Calc organising recalculation cannot know exactly about cells needing recalculation due to probable changes in the parameters. Therefore these functions are treated as “volatile” and thus recalculate “on any change”. For large sheets this is inefficient and may afflict usability with AutoCalculate enabled.
Not just as a joke: A thoroughly developed database is the means of choice for your task. I don’t know anything about yours school. I myself was in charge for 10 years of doing things you are talking of for a school of up to 100 teachers and 1150 students. It wouldn’t only have been inefficient to do it spreadsheet-based, but also unlawful due to legal norms of my country/state concerning data security, data safety, and privacy.
I also well know the disadvantages coming with the obligation to use a closed ready-made database system. Life is hard, after all.
Thank you both for your answers! It seems that you are really experienced users! The problem is that I use this spreadsheet for both storing data (information about the students) and for creating the timetable for my school (which needs all this information). For creating the timetable, I used the spreadsheet “approach” since I know how to check for errors. Can a database system cover both my needs? The spreadsheet checks for the timetable PLUS the statistics? Thanks again!
I neither know your country nor anything about the school system you are working in or any specifics (number of teachers and students eg) of your school. In the system and for the specific school i mentioned, the creation of a “time table” (detailed schedule for anything related to teaching classes) needed to be split. A first task was to assign resources (teachers and rooms mainly) to the planned/needed lessons. The second part then was the detailed scheduling. Under the specific conditions I have experiences with I had no chance to do that all with one homemade bundle of spreadsheets, even if I resorted to unlawful means. For the first part I had to work with two databases (this was abandoned meanwile) and some intermediary spreadsheets and some special “hardware” for “creative” work. For the second part I passed the results to a specialised program and to a person specifcally skilful concerning that task.
My country (de-BY) loves complicated optimisation.
Thanks again!
Some further details:
My school has many private-like musical instrument lessons. This means one teacher teaches to one student (or sometimes to 2-3 students). Of course, I can use a specialized timetabling application, but this is really complicated. Even for just entering the data, I need too much time.
So, in order to create the timetable (detailed schedule of my school), I use a spreadsheet and I assign each student to his/her teachers. The spreadsheet is “programmed” so that I can see when a student is not available or not, I can check if all students have the correct lessons and the correct number of hours, if the teachers have the correct number of hours, etc. Of course the spreadsheet carries all the needed information. I.e. group for each one of the students, musical instrument, teacher name, etc. For the part of information needed (and the statistics), a Database is more efficient. But, can the Database “replace” the spreadsheet for creating the timetable?
Yes. If this is for up to 80 or 100 students, and you are decisive to solve upcoming problems again and again, you may do it with spreadsheets (the privacy complex aside). From my experience it’s much easier to work in a creative way with spreadsheets (as compared with database frontends) - and it’s even a bit of fun sometimes. It’s much more complicated and error-prone however concerning data-safety, security (including backupping and all that). It brings hard responsibilties.
To advise concerning the how is difficult. There are only a few principles like:
Don’t mix up different functionalities in one sheet.
In sheets to keep (and maintain) data regard te most basic principles of databasing and in addition some only applying if working with spreadsheets (except when re-building completely):
- Don’t physically delete. Mark “deleted” instead.
- Don’t change sort-order in data sheets. (Sort with option ‘Copy sort results to’ to get sorted indexes if needed)
- … (depending)
- Study the database-like tools of Calc like filters and specifically pivot tables.
- Avoid user code (with few exceptions).
Concerning database principles you may read something like https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/ eg. (I didn’t study this thoroughly myself, however.)
Thanks a lot!