No, a cell cannot be >0 and <0 at the same time. 
My formula is an attempt to combat empty cells.
of course …sorry my fault!
1 Like
Or a formula that works independently of the language.
=AGGREGATE(2,6,IF((WEEKDAY($A$9:$A$140,2)=1)*($J$9:$J$140<>0)))
How do you match an empty cell, like replacing the zero:
=COUNTIFS(A9:A140,"Monday",J9:J140,"<>""")
Can you use backslashes?
=COUNTIFS(A9:A140,"Monday",J9:J140,"<>\"\"")
(LeroyG added Preformatted text to the formulas)
=COUNTIFS(A9:A140,"Monday",J9:J140,"<>")
That does not appear to work…
Is comma the correct separator for you? For me it would be a semicolon.
What’s wrong?
You are right; the help does show semicolons. Apparently it [mostly?] works with commas too. I will try it with “<>”…
I tried to change my commas to semicolons, but search and replace wasn’t working and in fact when I just edit a cell (like H7 in QQQ 3-5D), it just changes them back to commas. The odd things is, it works fine with the commas, but then changes from the 7 it should be to 30 because of all the Mondays in column J that have zeroes.
Because column J has this formula:
=IF(I53<=0,0,I53-G53)
Now if I go to every “Monday” cell in Col J and hit delete, I get my correct number of 7.
But if I change the formula in Col J to:
=IF(I53<=0,"",I53-G53)
then each unused cell does indeed show blank, but I still have a count of 30.
Is that because when it looks for nothing, “<>”, it is actually matching the formula of the cell, and so does not count it as blank?
Yes.
Backslashes don’t escape a double quote character, doubling them does, so
=COUNTIFS(A9:A140,"Monday",J9:J140,"<>""""")
but that is not what you are looking for, because it would match only cells not containing a string with two literal " double quote characters.
Whether to use the comma or semicolon function parameter separator depends on the locale you work in; in locales that use comma as decimal separator it can’t be used as parameter separator, but using semicolon works in all locales, hence we use it here for examples, even if afterwards it will be converted to the set parameter separator, e.g. comma.
Any idea why that does not work then? (see second half of previous post)
Okay, I must have misunderstood. You said:
I thought “<>” means ‘not equal to blank’, but you were saying it’s ‘not equal to non-blank’?
Perhaps it would be easier if you can give me the right formula. So I want to match rows that have Monday in the first column AND are NOT blank (in Col J), and thought it was this:
=COUNTIFS(A9:A140,“Monday”,J9:J140,"<>")
Can you correct it for my intent?
Sorry, I give up. This jumping between different details in the same topic and changing question makes me dizzy and I got derailed with whatever, I don’t even know. You’re right, "<>" is not equal to blank cell. Which includes cells with a formula. I’ll correct above.
Hey, if you’re burned out, I understand. Thanks for your responses. I’ll monitor to see if someone else can answer. And otherwise…not the end of the world to have to match zeroes.
Regarding the third option, that’s a little out of my league, but appreciated.
Yes, I was thinking of going back to try the regex route. But since I have to enable regex, does that mean if I read the SS into a different installation (where it was not set), that it would fail? i.e. Are those settings per-document / saved in the document?
And… I must have misread the COUNTIFS + COUNTIFS earlier. I like that; seems elegant.
You can learn. It is not that complicated to understand:
.
Start with:
WEEKDAY($A$9:$A$140,2)=1 This “selects” one Weekday from your range, and the ,2 decided, wich calender is used. And checking Help for WEEKDAY() reveals 1 for Monday. So we have now an “internal column” with all Mondays.
.
The second step is the
*($J$9:$J$140<>0) It is nomething used quite often to use “math” instead of IF: It applies a condition, so transfers all values in the range, which are not represented as 0 to TRUE, and as true is internally 1, you have a second range with 0 and 1 wich is multiplied with the first (row by row). This combines the two conditions, so the result is an internal column with a 1 for all Mondays, where column J was not equal to 0.
.
The last step actually counts:
AGGREGATE(2,6, Check the Help for AGGREGATE(). It reveals what to do with data (2 means COUNT) and what to ignore (6 ignore only errors).
.
The important part for you is then:
If you wish to change the range, you need to do this two times, once for A and once for J column.
And you may adapt the condition for the weekday to your needs in the part ,2)=1 or completely change condition …
https://help.libreoffice.org/latest/en-US/text/scalc/01/func_weekday.html
https://help.libreoffice.org/latest/en-US/text/scalc/01/func_aggregate.html?DbPAR=CALC#bm_id126123001625791
1 Like
It does… Took me hours to redo all the formulas the way I preferred, but it worked!
Thanks to everyone who contributed.