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.