How Do I Use Countifs In A Calc Macro?

[Solway-Sam]

I’ve been trying to change an Excel macro into a Calc one. The puropse is to count rows of data only when there are certain entries in three different columns. Here’s my original code where WordCol, DirectionCol and NumberCol are integers refering to the numbers of the columns I want to use and Sheet1 = ThisComponent.Sheets(0):

TotalCount = WorksheetFunction.CountIfs(Sheet1.Columns(WordCol.Column), “Yes”, Sheet1.Columns(DirectionCol.Column), “Up”, Sheet1.Columns(NumberCol.Column), 1)

I found this question about using optimalWidth in a macro and can use this code to change the width of the columns (it works as expected for all three columns):

Sheet1.getColumns().getByIndex(WordCol).OptimalWidth = true

Using that style of column selection in my original code still gives an error “BASIC runtime error. Object variable not set.”:

TotalCount = WorksheetFunction.CountIfs(Sheet1.getColumns().getByIndex(WordCol), “Yes”, Sheet1.getColumns().getByIndex(DirectionCol), “Up”, Sheet1.getColumns().getByIndex(NumberCol), 1)

I guess I must be using countifs wrong but couldn’t find any information on how to use it in a macro. So, how do I use countifs in a calc macro?

[Solway-Sam]

I changed my sub using the answer given by Ratslinger. It works for the first “if” but how do I use multiple criteria? Here’s the bit of Ratslinger’s code I changed:

    dim aArgument(5) as variant
    aArgument(0)=myRange1
    aArgument(1)="Yes"
    aArgument(2)=myRange2
    aArgument(3)="Up"
    aArgument(4)=myRange3
    aArgument(5)=1
End sub

This gives the error:
“BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.RuntimeException
Message: .”

[Ratslinger]

From your question it is not exactly clear what you are searching for. Here is a macro using COUNTIFS to find the number of cells containing a value of “20” or more in a range of “A1:A10” on sheet “0” and displays the total:

Sub TestIf
    dim oFunction as variant
    oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
    oDoc = ThisComponent
    oSheet = oDoc.Sheets.getByName("Sheet0")
    myRange = oSheet.getCellRangeByName( "A1:A10" )
    dim aArgument(1) as variant
    aArgument(0)=myRange
    aArgument(1)=">=20"
    TotalCount = oFunction.callFunction( "COUNTIFS", aArgument() )
    MsgBox TotalCount
End sub

For more information on macros, the most complete reference/guide in one document is Open Office Macros Explained (OOME) by Andrew Pitonyak (click here). You can download the PDF version there.

Edit: the code using your conditions with data in columns A, B & C rows 1-6:

Sub TestIf
    dim oFunction as variant
    oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
    oDoc = ThisComponent
    oSheet = oDoc.Sheets.getByName("Sheet0")
    dim aArgument(5) as variant
    myRange = oSheet.getCellRangeByName( "A1:A6" )
    aArgument(0)=myRange
    aArgument(1)="Yes"
    myRange = oSheet.getCellRangeByName( "B1:B6" )
    aArgument(2)=myRange
    aArgument(3)="Up"
    myRange = oSheet.getCellRangeByName( "C1:C6" )
    aArgument(4)=myRange
    aArgument(5)="1"
    TotalCount = oFunction.callFunction( "COUNTIFS", aArgument() )
    MsgBox TotalCount
End sub

You were close, just needed to specify the additional ranges.

[Solway-Sam]

Thanks a lot for the help. Imagine three columns with data like this:

Yes	Up		1
No	Up		0
Yes	Down	1
Yes	Up		0
Yes	Up		1
No	Down	1

I’d like to count the total number of rows with “Yes” in the first column, “Up” in the second and “1” in the third.

[Solway-Sam]

You’re a genius, thanks!