After update Sumifs function returns blank cells no error or value

English
#1

Calendar.ods (30.8 KB)
this file has been saved in version 7.2.7 and is not working
Calendar_version_6.ods (27.1 KB)
this file shows the desired values but after changing the month they disappear…

Hello friends,
in LibreOffice 6 my formula is working.
Given the month in cell m1.
Sumifs counts the number of days with according letter in the calendar beneath.
In LibreOffice 7 it returns only blank cells. Didn’t find any clues why, if its a bug or a feature :slight_smile:
Any advises except from do not upgrade to 7 are appreciated.
English is not my native language, sorry for any spelling or grammar errors.
Answers could be also in German or Polish if they solve the problem i will translate them to English.

P.S: The System is Windows 11 Home 21H2 (x64)
The version that is Working is 6.0.5.2
7.2.7.2 is my upgraded version, not working, tried also 7.4.0 and 7.3.6 also not working
My regional setting is pl-PL(pl_PL)
The formulae in the cells in b1 to b4 till J1 to J4 are problematic, they should not be empty for month 8.

Perhaps I’ll add an explanation what i am doing here :slight_smile:
In this file I’m making a calendar for nine people Column B to J(row 6 names), they have different things to do each day C, D, N, U or nothing x. Now cells B1 to B4 till J1 to J4 are counting each task for the selected month for everyone and row 5 is giving the sum of days for the whole month. Hope this helps …

Problem solved, my new formula is
=SUMPRODUCT($O$7:$O$100;$A$7:$A$100>=DATE($M$2;$M$1;1);$A$7:$A$100<DATE($M$2;$M$1+1;1);C$7:C$100=“D”) where m1 is the month and m2 is the year…
works. Was to dumb to get EDATE working.:slight_smile: Thank u @mikekaganski for the SUMPRODUCT. Still curious why the old formula stopped working. :slight_smile:

#2

Please, basically you have to specify the operating system and the LibreOffice version (three digits, e.g. 7.3.2).
Thanks.

#3

Thanks, edited my post.

1 Like
#4

Additionally:

  1. Which cell to look at. The sheet is rather large, and anyone who wants to help needs to know where to look, not check everything trying to figure it. You only mentioned m1 with month number, not the cell that the formula is in.
  2. Please post full info from help->about - it e.g. contains your locale, which might be relevant here.
#5

Tools>Options>Calc>View
Show Zero Values = ON (was off)

#8

Thanks, edited again :slight_smile: … but we are getting there slowly.

#9

Thanks, yes i do not like the zeroes :slight_smile: but i need the values >0 …

#10

I tested your file with LibreOffice 6.0 and 7.2 without noticing any difference. The formula cells showing a blank result were hidden zero values.

1 Like
#11

The formula looks odd.
You intend to sum cells with 1s in column M (is it meant to simply count the rows, in which case you could slightly simplify by using COUNTIFS, and dropping M?); and the rows must be in the wanted month. You create a mask like ="*.**"&M1&".2022" - but the mask looks odd, having two asterisks - why?; and it depends on the date being represented specifically as D(D).M(M).YYYY - which would require some specific locale. And this depends on the match being tested on the default converted value …

Better use a SUMPRODUCT.

=SUMPRODUCT($M7:$M56;$A7:$A56>=DATE(2022;M1;1);$A7:$A56<EDATE(DATE(2022;M1;1);1);C7:C56="D")
1 Like
#12

Thanks you where right i translated the file in version 7 and did not check now it isn’t working in 6 anymore.
Calendar_version_6.ods (27.1 KB)
This file has been edited in version 6 and shows the values but as soon as you change the month they disappear. :frowning:

#13

Thanks I will look into Sumproduct … perhaps its time to change.

#15

Hi, must lok into this formula but it seems there is a typo <EDATE has to be DATE never the less i get a
511 error missing variable. I’ll work on that this night :slight_smile:

My “odd” formulas have worked for me the last 3-4 years and since i stick to not changing something that works i never had the need to evaluate something else. :slight_smile:

#16

No. EDATE is a function that adds months to a given date; and when you must make sure that your dates are in a given month, you must find the first date of the next month to check that dates are less.

1 Like
#17

EDATE gives me a name error … :frowning:
even when i copy and paste the the example from the documentation.
=EDATE(“2001-03-31”;-1)
that’s why i thought it was a typo … sorry

#18

Zoom in on the browser and you will see the “strange” double quotes in your example. Excel formulas do not understand this. :slightly_smiling_face:
Also, function names in Calc are localized (unless you set the appropriate option for English function names).

1 Like
#19

I suppose that the typographic quotes are just an artifact of pasting here; more probable origin of OP problem is use of localized function names? See Options|Calc|Formula; check Use English function names for maximum international ineroperability.

#20

Hi again … Did try to omit edate with =SUMPRODUCT($M7:$M56;$A7:$A56>=DATE(2022;M1;1);$A7:$A56<DATE(YEAR(M1);MONTH(M1+M2);DAY(M1));C7:C56="D")
put a 1 in M2
No error … empty cell again !?
Ok, did to many things at once This Formula was simply bad …
=SUMPRODUCT($M7:$M56;$A7:$A56>=DATE(2022;M1;1);$A7:$A56<DATE(2022;M1+1;1);C7:C56=“D”) without edate

#21

Hi, have Use English function names enabled. This strange double quotes came from copy/paste from
EDATE example

#22

What was that? Your M1 is used as just a month number in the first DATE, but as a date in calls to YEAR, MONTH, and DAY…

#6

Is it wildcards in formulas that you need to set? Tools > Options > LibreOffice Calc > Calculate. It fills in values in columns K & L

WildcardsInFormulas
WildcardsInFormulas728×492 16.1 KB

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