Countif in VBA with Multiple Conditions

Hi,

I am trying to create a VBA Countif function with an OR condition inside if one of the following values is found. For instance, in this code:

numerator = WorksheetFunction.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), red, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), BrandArray(i), _
                    .Range(.Cells(2, purposeColumn), .Cells(LastRow, purposeColumn)), 1, _
                    .Range(.Cells(2, materialColumn), .Cells(LastRow, materialColumn)), Array(1, 2, 3, 7, 8, 9, 10))) + _
                    WorksheetFunction.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), blue, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), BrandArray(i), _
                    .Range(.Cells(2, purposeColumn), .Cells(LastRow, purposeColumn)), 1, _
                    .Range(.Cells(2, materialColumn), .Cells(LastRow, materialColumn)), Array(1, 2, 3, 7, 8, 9, 10)))

Here, the numerator would contain the count of cells with colors red or blue in the colorColumn, for the i index in the BrandArray in the brandColumn, with a purpose of 1 within the purposeColumn, with the material codes of 1,2,3,7,8,9, or 10 in the materialColumn. But I must be doing something wrong because the code only checks for 1 in the materialColumn, not the full list of numbers (the rest of the code works as normal).

Do I have the syntax wrong for how to perform this?

Thank you.

The syntax looks fine to me.

Originally Posted by Dord25

Thank you. Is there any other way to write Array(1, 2, 3, 7, 8, 9, 10) to check if this is the problem, if this is supposed to be the right way to count multiple numbers here?

Try changing the order of the numbers in the array to see if it changes the result. If it doesn’t, then that is not the issue.

Are red and blue words in a column on the sheet or variables set to some number in your code or cell background colors?

When I did a small test on: A 0 0 7 0 7 9 8 1 0

=COUNTIF(B2:I2,{7,9})
the formula only returned a count of the occurrences of first value in the array (i.e., 2)
=COUNTIF(B2:I2,{9,7}) --->1

sample file, maybe?

If you want the count of 7 and 9 combined, you'd need this:

Formula:

=SUM(COUNTIF(B2:I2,{7,9}))

You're welcome. Thanks for the rep.

No, if that were the case it just wouldn't work for any of them. I'm assuming that red and brandarray(i) are not themselves arrays? ( I Know brandarray is an array, but am assuming the items within it are not also arrays)

Can you post a workbook with the code where it doesn't work for you? (only the minimum code required to demonstrate the problem)

I've attached a minimal sample file. Can you just verify that running the foo macro produces a message with the same number as the formula on the sheet?

Originally Posted by Dord25

Thanks for the worksheet. Yes, I get 3 on the sheet. But when I add another condition like so:

MsgBox WorksheetFunction.Sum(Application.CountIfs(Range("A1:A28"), "Red", Range("B1:B28"), "Test1", Range("C1:C28"), Array(1, 2, 5, 6), Range("D1:D28"), Array(1, 3)))

I get 1, when we should get 2.

Attachment 855466

One array needs to be transposed - for example:

Application.Transpose(Array(1, 3))

Yes, that is true but it is not the case in your code (that is why I asked if either of the other conditions were arrays).

If you need two arrays, one has to be a vertical array and the other horizontal

Originally Posted by rorya

Yes, that is true but it is not the case in your code (that is why I asked if either of the other conditions were arrays).

No, they are not, but BrandArray is an array as well. It is variant type and contains codes like 1453, 1698, etc. that are matched on.

The column I added to the file has the same kind of format of the column in the code above that is not working (with Array(1, 2, 3, 7, 8, 9, 10))

Originally Posted by rorya

If you need two arrays, one has to be a vertical array and the other horizontal

Sorry, I am confused. Is there some documentation on what kind of rule this is? How would it work for the foo macro? Thank you.

Originally Posted by Dord25

No, they are not, but BrandArray is an array as well.

Yes, I know, but you are not passing the whole array at once as a criterion, you are passing individual elements one at a time using BrandArray(i). Unless the individual elements of your BrandArray array are also themselves arrays, that is not the issue (your code is posted completely out of context so we can't tell).

Sorry, I am confused. Is there some documentation on what kind of rule this is? How would it work for the foo macro? Thank you.

No, because it's not a normal use of countifs. If you use two arrays with the same orientation, the criteria are taken in pairs but what you want is all possible combinations (a Cartesian product). So for example, if you pass the arrays {1,2} and {3,4} as criteria, you would get rows matching 1 and 3, and rows matching 2 and 4 only. You would need the equivalent of {1,2} and {3;4} in formulaic terms.

Thanks for the arcane tip. I did not know about this rule. Is this because of how Countif operates?

Here is the full loop:

For i = LBound(brandArray) To UBound(brandArray)
    
    With Worksheets(FirstSheet)
        
        'looks up the gender
        female = WorksheetFunction.IfError(WorksheetFunction.index(.Range(.Cells(2, Female_Column), .Cells(LastRow, Female_Column)), _
                        WorksheetFunction.Match(brandArray(i), .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), 0)), "")
        
        male = WorksheetFunction.IfError(WorksheetFunction.index(.Range(.Cells(2, Male_Column), .Cells(LastRow, Male_Column)), _
                        WorksheetFunction.Match(brandArray(i), .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), 0)), "")
        
              
       
        If female = "Y" Then
        
            
            'numerator1 looks at i in brandArray, with sizes 2,3, and 4, with purpose code 1, for both red and blue.
            numerator1 = WorksheetFunction.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), red, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), brandArray(i), _
                    .Range(.Cells(2, sizeColumn), .Cells(LastRow, sizeColumn)), Array(2, 3, 4), _
                    .Range(.Cells(2, purposeColumn), .Cells(LastRow, purposeColumn)), 1)) + _
                    WorksheetFunction.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), blue, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), brandArray(i), _
                    .Range(.Cells(2, sizeColumn), .Cells(LastRow, sizeColumn)), Array(2, 3, 4), _
                    .Range(.Cells(2, purposeColumn), .Cells(LastRow, purposeColumn)), 1))
            
                      
        
            'numerator2 at i in brandArray, with sizes 2, 3, and 4, with material codes 1, 2, 3, 7, 8, 9, 10, for both red and blue
            numerator2 = Application.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), red, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), brandArray(i), _
                    .Range(.Cells(2, sizeColumn), .Cells(LastRow, sizeColumn)), Array(2, 3, 4), _
                    .Range(.Cells(2, materialColumn), .Cells(LastRow, materialColumn)), Array(1, 2, 3, 7, 8, 9, 10))) + _
                    Application.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), blue, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), brandArray(i), _
                    .Range(.Cells(2, sizeColumn), .Cells(LastRow, sizeColumn)), Array(2, 3, 4), _
                    .Range(.Cells(2, materialColumn), .Cells(LastRow, materialColumn)), Array(1, 2, 3, 7, 8, 9, 10)))
        
                      
            
            'numerator_intersection is the intersection of the two sets (purpose code and material code)
            numerator_intersection = Application.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), red, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), brandArray(i), _
                    .Range(.Cells(2, sizeColumn), .Cells(LastRow, sizeColumn)), Array(2, 3, 4), _
                    .Range(.Cells(2, purposeColumn), .Cells(LastRow, purposeColumn)), 1, _
                    .Range(.Cells(2, materialColumn), .Cells(LastRow, materialColumn)), Array(1, 2, 3, 7, 8, 9, 10))) + _
                    Application.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), blue, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), brandArray(i), _
                    .Range(.Cells(2, sizeColumn), .Cells(LastRow, sizeColumn)), Array(2, 3, 4), _
                    .Range(.Cells(2, purposeColumn), .Cells(LastRow, purposeColumn)), 1, _
                    .Range(.Cells(2, materialColumn), .Cells(LastRow, materialColumn)), Array(1, 2, 3, 7, 8, 9, 10)))
            
            'combined both numerators as OR condition for union of sets (to avoid overcounting)
            numerator = numerator1 + numerator2 - numerator_intersection
            
            
            'denominator consists of ALL brand i with sizes 2, 3, and 4, in either red or blue
            denominator = WorksheetFunction.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), red, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), brandArray(i), _
                    .Range(.Cells(2, sizeColumn), .Cells(LastRow, sizeColumn)), Array(2, 3, 4))) + _
                    WorksheetFunction.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), blue, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), brandArray(i), _
                    .Range(.Cells(2, sizeColumn), .Cells(LastRow, sizeColumn)), Array(2, 3, 4)))
            
                        
        'otherwise there is no gender
        Else: denominator = -1
        
        answer = numerator / denominator

        End If
            
    End With

So in numerator2 should we have:

numerator2 = Application.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), red, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), brandArray(i), _
                    .Range(.Cells(2, sizeColumn), .Cells(LastRow, sizeColumn)), Array(2, 3, 4), _
                    .Range(.Cells(2, materialColumn), .Cells(LastRow, materialColumn)), Application.Transpose(Array(1, 2, 3, 7, 8, 9, 10)))) + _
                    Application.Sum(Application.CountIfs(.Range(.Cells(2, colorColumn), .Cells(LastRow, colorColumn)), blue, _
                    .Range(.Cells(2, brandColumn), .Cells(LastRow, brandColumn)), brandArray(i), _
                    .Range(.Cells(2, sizeColumn), .Cells(LastRow, sizeColumn)), Array(2, 3, 4), _
                    .Range(.Cells(2, materialColumn), .Cells(LastRow, materialColumn)), Application.Transpose(Array(1, 2, 3, 7, 8, 9, 10))))

Yes. It would have saved a lot of time if you'd posted the relevant code to start with.

No, it isn't. You'd need a different approach.

It would depend on the context. It might be easier to loop through arrays, or use ADO, or filters, or something like a SUMPRODUCT formula inside Evaluate.