Excel: Using COUNTIF

[CE101]

COUNTIF is an Excel worksheet function that counts the number of cells within a range that meet the given criteria.

Syntax: COUNTIF(range,criteria)

Example: COUNTIF(A2:A5,"apples")

I would like to use this function in my script.

Googling around I've seen a lot of VB examples...

http://www.eggheadcafe.com/software/aspnet/30447507/count-if-range.aspx

Application.CountIf(Range("J3:J600"), "35")

http://forums.devshed.com/visual-basic-programming-52/countbetween-function-165810.html

Application.CountIf(Sheets("sheet2").Range("f2:f500"), "=16")

http://www.vbforums.com/showthread.php?t=618030

Application.CountIf(Columns("D:D"), date1)

www.vbaexpress.com/forum/archive/index.php/t-8121.html

Application.CountIf(Range("a1:a20"),"=5")

However I can’t seem to get the syntax correct for AutoIT.

Here's what I've tried...

expandcollapse popup

#include <Debug.au3>      ; included at top of pgm
    _DebugSetup ("" & ":  Debugger")
    #include <Excel.au3>

    Local $oExcel = _ExcelBookNew() 

    Local $aArrayB[6] = ["", "New York", "Los Angeles", "Chicago", "Boston", "Atlanta"]

    For $i = 1 to 10
        For $j = 1 to 5
            $var1 = Random (1, 5, 1)
            $var2 = $aArrayB[$var1]
            _ExcelWriteCell($oExcel, $var2, $i , $j)    
        Next
    Next

    $var3 = 99

    ; Count the number of occurences of "Atlanta"
    ; On each CountIf, $var3 gets set to 0...    Why?? 
    $var3 = $oExcel.Application.CountIf("A:E", "Atlanta")             ; no error msg, but returns 0
            _DebugReportVar("Test 1:  #recs found ... $var3", $var3)
    $var3 = $oExcel.Application.CountIf("A1:E10", "Atlanta")          ; no error msg, but returns 0
            _DebugReportVar("Test 2:  #recs found ... $var3", $var3)
    $var3 = $oExcel.Application.CountIf("Range(A1:E10)", "Atlanta")   ; no error msg, but returns 0
            _DebugReportVar("Test 3:  #recs found ... $var3", $var3)
    $var3 = $oExcel.Application.CountIf("Sheets(Sheet1).Range(A1:E10)", "Atlanta")   ; no error msg, but returns 0
            _DebugReportVar("Test 4:  #recs found ... $var3", $var3)


    ; Even if garbage is entered into argument #1 (range), there is no error msg
    $var3 = $oExcel.Application.CountIf("Range(junk???)", "Atlanta")   ; no error msg, but returns 0 
            _DebugReportVar("Test 5:  #recs found ... $var3", $var3)


    ; Countif needs 2 arguments. If you have only one argument or you add an extra one, it fails
    $var3 = $oExcel.Application.CountIf("Range(junk???)")   ; error, "requested action with this object has failed"
    $var3 = $oExcel.Application.CountIf("Range(junk???)", "Atlanta", "junk????")   ; error, "requested action with this object has failed"

As you can see from the examples I've tried, Excel definitely recognizes it as a CountIf. That's why it objects if the correct number of arguments are not present. So the question is -- why doesn’t it give the correct results when the correct number of arguments are not present.

Any suggestions would be greatly appreciated. Sample code would be even better.

[water]

I think you have to write the formula into a cell. I use it this way:

$oExcel.Activesheet.Range("B1").Formula = '=COUNTIF(C1:IV1;"X")'

Cell B1 then contains the number of cells in the range from C1 to IV1 that contain an "X".

[JoHanatCent]

Maybe if you specify the Excel range?

#include <Debug.au3> ; included at top of pgm

_DebugSetup("" & ": Debugger")

#include <Excel.au3>

Local $oExcel = _ExcelBookNew()

$oExcel.Sheets("Sheet1").Select ; ===> Just to select "sheet1"

Local $aArrayB[6] = ["", "New York", "Los Angeles", "Chicago", "Boston", "Atlanta"]

For $i = 1 To 10

For $j = 1 To 5

$var1 = Random(1, 5, 1)

$var2 = $aArrayB[$var1]

_ExcelWriteCell($oExcel, $var2, $i, $j)

Next

Next

$var3 = 99

;=== Maybe Change this into an Excel Range :

$var3 = $oExcel.Application.CountIf($oExcel.range("A:E"), "Atlanta")

;=== End of Change ; }

_DebugReportVar("Test 1: #recs found ... $var3", $var3)

MsgBox(64, "Result:", $var3, 3)

; === Can also write the result to the sheet:

$oExcel.Range("G5") = $oExcel.Application.WorksheetFunction.CountIf($oExcel.Range("A:E"), "Atlanta")

; === End of writing to sheet

[GMK]

For example, if you wanted to write the formula in cell F1, use:

_ExcelWriteFormula($oExcel, '=COUNTIF(A1:E10, "Atlanta")', 'F1')

If you don't need the actual formula in the worksheet, just write the formula temporarily to get the value from the cell, then delete the formula later.

[CE101]

Thank you all for your suggestions. Problem solved.