Cannot export formula to .xlsx spreadsheet using c#

Question

So I'm trying to use COUNTIF function to reference several different worksheets and count all occurrences of a keyword. I'm using the C# NuGet Package ClosedXML to export the data and the formula, but every time I do so, I get the following error in Numbers:

The formula couldn’t be imported and was replaced by the last calculated value. Original formula: =COUNTIF(2 WHATUP::Table 1::A2:A85,A6) + COUNTIF(3 Movies::Table 1::A2:A28,A6)

Excel gives me something similar. The only way to get it to accept it is to copy the formula, clear the cell, and paste the formula back. Here is the formula exactly as it is exported within my C# code (disregard the test worksheet names):

COUNTIF(2 WHATUP::Table 1::A2:A85,A4) + COUNTIF(3 Movies::Table 1::A2:A28,A4)

I've also tried the ! notation:

COUNTIF(2 WHATUP!A2:A85,A2) + COUNTIF(3 Movies!A2:A28,A2)

I've also tried using SUM to add them both together instead of +:

=SUM(COUNTIF(2WHATUP::Table 1::A2:A85,A2),COUNTIF(3Movies::Table 1::A2:A28,A2))

Yes, I've even tried adding more arguments within a single COUNTIF. This returns the same error as well.

=COUNTIF(1 Jello::Table 1::A2:A328, 3 Movies::Table 1::A2:A28, A2)

The only time I can get it to work is when I only use one COUNTIF to calculate a single range (without adding the results of another COUNTIF). But I need to add together the occurrences of a keyword throughout several worksheets, hence the use of several COUNTIFS/several arguments within a COUNTIF.

Please help! I have tried everything I can think of.

Thank you so much.

Answer 1

Looks like you have some space characters in the worksheet names, which then require beeing enclosed by single quotes.

Adopting your second example, following should work just fine:

=COUNTIF('2 WHATUP'!A2:A85,A2) + COUNTIF('3 Movies'!A2:A28,A2)