VBA: How to countif cell shouldnt be empty and corresponding cell shouldnt be equal to a specific text?

[shrinivasshweta]

[COLOR=#333333]arr1=array("d","e","f")[/COLOR]
[COLOR=#333333]For Each elem In arr1[/COLOR]
[COLOR=#333333]a= a + WorksheetFunction.Sum(WorksheetFunction.CountIfs(.Columns(2), "<>specific text", .Columns(13), "<>""", .Columns(7), elem))[/COLOR]
[COLOR=#333333]Next elem[/COLOR]

Can someone tell me a better way to do this?
Thank you so much

 

[My Aswer Is This]

I think it's always best to tell us in words what your attempting to do. And not show us a script you have that does not do what you want and expect us to fix your script.

Tell us in detail what your attempting to accomplish.

We need Sheet names column numbers and criteria.
And where you want the results entered.

 

[shrinivasshweta]

I want to count the total number of a's based on following criteria:

1)cell in Col B shouldn't be equal to "specific text"
2)corresponding cell in col M shouldn't be blank
3) corresponding cell in col G should match with elements of following array
array= "d","e","f"

 

[shrinivasshweta]

Can someone please help me write this logic in VBA??

Please gurus. I really need help.

 

[Tetra201]

Try this:

    With Sheets("YourSheet")
        For i = 1 To Rows(.UsedRange.Row + .UsedRange.Rows.Count - 1).Row
            a = a - CLng((.Cells(i, "B").Value <> "specific text") And _
                (.Cells(i, "M").Value <> "") And _
                (Not IsError(Application.Match(.Cells(i, "G").Value, Arr1, 0))))
        Next i
    End With

 

[shrinivasshweta]

The output is 0 for a.

The columns H to M are merged in some cases. Can this be a problem?

 

[shrinivasshweta]

Thank u so much. It works perfectly. Thanks a lot... :D

 

[Tetra201]

You are most welcome.