SUMIF criteria
Wes
=SUMIF ( YEAR ( C11:C14 ) , 2016, B11:B14 )
but note that the functions should be entered using the function wizard and check the Array checkbox at the bottom left of the wizard.
Year(C11) in an empty cell and there is a date in cell C11. If cell C11 is empty, I get the number 1899. Use format cells for C11, make sure category is Date, check format string against the displayed value, and if these two formats do not match then LibreOffice has not recognized the data entered as a date.
If you change the date format of the cell to match the format of your data, then LibreOffice will insert an apostrophe at the first character of your data and continue to treat the data as text. After changing the date format you need to remove the apostrophe. I tried using find and replace but this doesn’t seem to work in this case.
If you have a lot of dates to re-format, one thing I found to work was to format a different cell as the date format to match your data, then set that cell = C11. So you could probably create a new column, set it’s date format, use = to set to data from your existing column, then copy and paste that new column to itself as values to remove the formula, and then remove the old column.
Hopefully someone with more experience in LibreOffice has a better solution for correcting the date format of already entered data.
RIGHT(CELL,4) and can get it to return “2014” so it would seem that I should be able to use this in combination with SUMIF in some way to get the desired results. Any thoughts on this approach? Thanks again.
Wes
=SUM ( IF (RIGHT ( C11:C14 , 4 ) ="2015", B11:B14 ,0 ) )
Need to make sure to test the result against “2015” instead of 2015, and still need to set the Array checkbox in the function wizard.
If you set the ‘Numbers’ format of a cell to anything, e.g. “M/YYYY”, this will not mean anything for the content. If the content is of type text the ‘Numbers’ format will not afflict at all the display. Use ‘View’ > ‘Value Highlighting’, and you will get numbers in blue, text in black, and formula results in green. You may also ask any cell, say A1, by =ISTEXT(A1) whether its value (constant or result) is text. …
Without sufficient info we are just stabbing in the dark. Knowing the actual situation this is surely very simple.
Wes
I’ve also seen some strange characters inserted by copy and paste from forum posts, different quote characters seems quite common.
Also I find I need to add spaces when posting on this forum to avoid 0 ) showing as 
etc.
I just want to thank you again for taking your time to try and help me with my problem. As a result of your suggesting to try some things and comments you have made, I was able to eventually figure out what was going on and why, and more importantly, come up with the formula that worked! Thanks again for your assistance! It was key in getting my spreadsheet to work. Thank you, thank you.
Wes
It is also a place where LibreOffice users having a problem are looking for a solution. In many cases they accept the suggestion to first look for advice already available from stored threads, instead of readily post a new question.
Such users would surely appreciate to find, at the end of a thread, not only a “Done!”, but also a clarification about what had actually caused the problem, and in what way, exactly, it was solved. An additional [SOLVED] prefixed to the original question by editing, would also be applauded.
Currently installed: LibO V5.1.2 on Windows 10
Maybe we might! (Create a powerful UFO: United Free Office!) Lupp from München