CountIF doesn't find time in list of formula-generated times (worked in Excel)

[ADHDad76]

Hello!

I’m a little new to LO but so far it seems fantastic… mostly I’m moving to LO (see end of post for version info) from MSO (MS365, latest update as of today’s date) (tired of “renting” “my” software), so I’m moving files I use regularly to LO. I have a spreadsheet in which I track viewers of our streams. The file has several tabs (sheets, sorry), and nearly everything is working as expected and as it did in Excel. I re-create (read save a copy and edit/remove values as needed) this file every year. In the process of doing this, I discovered one of the things I track isn’t tracking any more, although it worked fine in Excel.

More specifically, the end result of this process is the issue (please bear with my use of too many words): (NOTE: all relevant cells are formatted as NUMBER:GENERAL)

1. Enter numbers into each cell of 1 row of 5 columns, each of which is headed by a time (8:00, 9:15, 10:45, Noon, Evening) (cells N5:R5; headers are N2:R2)
2. Automatically populate cell T5 using MAX(N5:R5)
3. Automatically populate cell S5 using XLOOKUP(T5,N5:R5,N$2:R$2,“blah”) (which works fine, by the way)
4. This is where I have a problem: Automatically update cell W3 via COUNTIF(S$3:S$56,V3); The range contains a mix of times and words (see list in step 1) and cell V3 contains a constant that matches one of those times (technically we are updating W3:W7 but the formulae are the same [except reference rows] so theoretically if I can fix one I can fix them all). This stubbornly returns 0 (zero) no matter what despite working properly in Excel.

I have tried everything I can think of:

• Re-typed formula instead of leaving original Excel formula
• Used Formula Wizard to create formula
• Entered formula as an array formula (using CTRL-SHIFT-ENTER)
• Replaced COUNTIF with SUMPRODUCTS
• Wrapped COUNTIF with SUMPRODUCTS
• Tried with a subset of S3:S55 that contained only times, no words
• Added quotes to V3 (""&V3&"" and “”""&V3&"""")
• Ensured typographer’s quotes were not being used anywhere
• Replaced the comma with a semicolon (Calc just turned it back into a comma every time)

As far as I know, the only thing I haven’t tried is the INDEX(MATCH()) method because 1) I don’t want to and 2) see 1).

This is very frustrating because I can’t see any reason this shouldn’t be working. Hopefully one of y’all with more LO/Calc experience than I have will be able to provide an answer!

Thanks for reading and TIA for any help you can offer.

(Uploaded copy of sheet (copied into its own workbook); area of interest is bordered since I left all the data in.)

Testfix.ods (38.1 KB)

Version: 7.6.4.1 (X86_64) / LibreOffice Community
Build ID: e19e193f88cd6c0525a17fb7a176ed8e6a3e2aa1
CPU threads: 6; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

[Lader]

Testfix.ods (41.1 KB)
Removed apostrophes ahead of time

[Zizi64]

The function XLOOP() comes from a third party Extension:

Lox365: XLOOKUP for LibreOffice » Extensions

(I have not installed it.)
Try to ask the author of that extension.

[ADHDad76]

Thank you to @Lader and @Zizi64. It looks like XLOOKUP() is the likely culprit. I will attempt to contact the author as @Zizi64 suggested; meanwhile I’ll use @Lader’s modification (or change methods). I never suspected XLOOKUP; if I thought about it, I knew it was an add-on because it wasn’t in the official documentation. But because the extension installed automatically and I didn’t have problems with it, I didn’t even think of it.

Many thanks to you both again!

[Zizi64]

A note for the incompatibility: Lader’s solution uses the IFERROR() function. If you send this fixed sample to a person, who uses ApacheOpenOffice, the sample file will not work, because the AOO Calc has not such function. But you can use the combination ot the IF() and the ISERROR() functions, what exist in the AOOCalc/LOCalc/MSExcel.

[EarnestAl]

I just installed Xlookup, I also changed the formatting of the Peak Concurrent Views by Service headings to Time and put same under Service, yellow highlight. The Countif formula now works.

What I find is that Xlookup does what VLOOKUP does and finds the first match, the INDEX,MATCH finds a different match if two values are equal. This gives different totals for #Peak; For 8:00 I get 39, INDEX,MATCH gets 37, etc . I don’t know what totals Excel calculates and how the results match existing calculations.

Testfix100135EA.ods (35.3 KB)

[ADHDad76]

Interesting… I thought I tried that as well. Thank you!