@clear
What if the table has all the cells based on a formula, and the cells are updated each day based on the data i introduce in some other sheet? That means that no cells are empty. Does that help?
I was wondering if there is some kind of formula based on the today() formula, to get what i’m looking for until the current day? Obviously the result would change every day.
1 Like
@emy1
Another question. What kind of experience do you have with array formulas (which are different from regular formulas) ?
SUMIF(range;criterion;sum_range) is 40 years old and must not change.
SUMIFS is much younger. It takes one range to be summed and a variable amount of range=criterion pairs. Programming languages append variable argument lists to the fixed arguments.
1 Like
.
In that case I recommend going straight to @Lupp suggestion and getting it to work. It’s an elegant, beautiful solution. It uses Array Formulae.
.
His ods file worked fine for me under Win11 26.??? and Linux 25.???. There were no errors.
.
I presume @Ludd’s ods works fine for you under Mint 24.???.
.
The errors you are getting (you wrote: I get the #Value! error for the function that gives all the days) could be due to the very odd and hidden details of Array Formulae and putting one in a cell. Here is a good refresher for how to work with them:
.
https://help.libreoffice.org/latest/en-US/text/scalc/01/04060107.html
.
I won’t be of any help in this regard because I’ve never needed to use them, sorry. But I do suggest that you preserver with his winning approach.
I may not have studied all the suggestions and comments above thoroughly enough, but as I thought some additional remarks should be useful, I decided to create a new example based on what the original questioner had attached.
See attachment:
disask_132592_collect_show_matching_dates.ods (34.8 KB)
2 Likes
I get the #Value! error for the function that gives all the days.
Can you explain please why the calculations must be at the top of the sheet?
Is there something wrong if they are at the bottom?
Also, is there a problem if the table has many cells empty? We are in February and the sheet goes until November.
You didn’t tell what version of LibreOffice you are using.
The formulas in row 4 and in row 6 of my example use the TEXTJOIN() function which didn’t exist in LibO Calc before version V5.4 (2017-07-24).
No. You have the choice. Hovever I think there are obvious reasons to prefer this arrangement.
- For the view it 's simple this way to have the relevant results always visible with the help of the
freezesetting. - Scaling/resizing of the data collection is simplified. Also prepared, but not yet used rows (dates till november in the exampole e.g.) can help to make things simpler.
- I prefer this arrangement personally.
No. To the contrary if you mean bottom rows which already are included with the ranges used in formulas.
I’m using the 24.2.7.2 420(Build:2) version on linux mint.
Then I don’t understand the #VALUE! errors. I tested with some different versions without getting the problem.
1 Like
I checked already for all sorts of common mistakes, like extra spaces, cell format, …
… data types?