Worksheet Function.CountIfs(...) in C# return value is a 2 dim Object Array

Question

My Problem is that i want to use the WorksheetFunction method CountIf in C# for Excel.

And I'm always getting an 2 dim object array from that function

double[,] zn1 = wsf.CountIfs(cSheet.Range[cSheet.Cells[13, 1],
     cSheet.Cells[20, 1]], sName + sName);

(It works only with double[,] zn1 or var zn1, otherwise I'm getting an error that the object array obj[,] can't be converted to a double or double[], string[] etc.")

where wsf is defined as

Excel.Application xl = new Excel.Application();
Excel.WorksheetFunction wsf = xl.WorksheetFunction;

So when I run it, I'm getting a 2 dim object array, which has the same length as the Range, I'm looking for, and all entries contain the same number "-2146826273".

And If I convert the array to a 1 dim double array, the return is System.Double[].

object[] to = zn1.Cast<object>().ToArray();
object[] res = to as object[];
double[] sRes;
sRes = res.OfType<double>().ToArray();

All other WorksheetFunction methods are working well and without errors.

So my question is how, do I get an normal value for the WorksheetFunction method CountIfs(...)?

And yes I have to use WorksheetFunction methods and I can't enter it like a Formula with a string.

score 1 · Answer 1 · edited Jul 12 '16 at 14:48

1

Hmmm... I found the problem.

It has to be like this:

var xl = Globals.ThisAddIn.Application;
Excel.WorksheetFunction wsf = xl.WorksheetFunction;

and not like that:

Excel.Application xl = new Excel.Application();
Excel.WorksheetFunction wsf = xl.WorksheetFunction;

edited Jul 12 '16 at 14:48

Draken

3,134
13
34
54

answered Jul 12 '16 at 14:38

Daniel

31
8

It is interesting, would be great to know why this has worked. – Janis S. Jul 13 '16 at 10:47

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

Try checking your formula, the error number ("-2146826273") indicates that the problem is within the formula.

See here for formula error checking and particular error.

edited May 23 '17 at 11:51

Community

1
1

answered Jul 11 '16 at 14:43

Janis S.

2,526
22
32

The funny thing is if i write this formula in vba it's working fine. But i have to do it in C# I have checked the formula for that error and I tried to change the format for the cells but it's not helping. It says also that a closed Workbook could be that problem for ("-2146826273") = #VALUE!, but it's always open. – Daniel Jul 11 '16 at 15:14
As you said the other WorkssheetFunctions are working so that should not be the open workbook issue. I think I would start with a very simple CountIfs in the formula to verify if this very simple case gets through. – Janis S. Jul 12 '16 at 13:07
I have tried the formula with simple CountIfs like: "32", <32, simple words, and also with empty cells, but the error ("-2146826273") stays the same. I have also tried CountIf and it's not working. And if I use CountIf(s) like this: `Worksheet.Range[...] = "= COUNTIFS(Range Arg1, Object Arg2)";` it works fine. – Daniel Jul 12 '16 at 14:17

score 0 · Answer 3 · answered Oct 30 '19 at 12:42

0

Try using below code for the countif - this is working

using Excel = Microsoft.Office.Interop.Excel;
Excel.Application excelapp = new Excel.Application();

double count = excelApp.WorksheetFunction.CountIf(range, Criteria);

returns a double with count

answered Oct 30 '19 at 12:42

Sudheerkumar Duggireddy

1
1

Worksheet Function.CountIfs(...) in C# return value is a 2 dim Object Array

3 Answers3