Using COUNTIF across a range rather than a single column/row-CodePudding

I'm trying to count the number of times a word appears in a range of cells using COUNTIF.

The formula I have tried is =COUNTIF($A$2:$T$9,C7)

Which is incorrect, adolescence appears 4 times across my data set. The strange thing is I can see that correct result if I use the formula builder/inserter to check the formula:

Everything I've looked at so far has pointed me towards array functions (or Control-Shift-Enter) but this doesn't work either.

What exactly is happening in the 'Insert Function' box that's not happening in the formula bar?

CodePudding user response：

Alternatively, instead, using of many COUNIF() you can use FILTER() function with few other function to make it workable and avoid circular reference.

=SUM(--(FILTER($A$2:$T$9,MOD(SEQUENCE(,COLUMNS($A$2:$T$9)),2))=A2))

You can make it dynamic array to spill results automatically can use BYROW() lambda function like-

=BYROW(A2:A9,LAMBDA(x,SUM(--(FILTER($A$2:$T$9,MOD(SEQUENCE(,COLUMNS($A$2:$T$9)),2))=x))))

CodePudding user response：

If the COUNTIF() is outside the range then it counts correctly and will show so in the formula builder BUT it will show 0 if there is some other COUNTIF() which causes a Circular Reference. Only if all Circular References are removed (other COUNTIF()s within the range for example) then it will show the count correctly. As an alternative to check the formula builder you could switch to Workbook Calculation Manual and calculate just this one cell using F2 to see the correct result.

CodePudding user response：

When you first entered that equation, you almost certainly saw a circular reference warning. And, even if you ignored it, you should look at the bottom left where you'll probably see the following helpful indicator:

With circular references, Excel is very careful not to get caught in an infinite loop as it tries to follow all the dependency chains.

In the case where you have circular dependencies between each of a great many cells (as your case does), this escalates very quickly and I'd be surprised if Excel didn't just berate you and exit in protest. Or, more likely, it just sets them to zero since it warned you and you chose to ignore it :-)

The most likely reason it works in the dialog box is because that's not actually a cell that would cause a circular reference. It's not until the formula is placed into a cell does that occur.

The solution, of course, is to get rid of the circular dependencies, by removing the count columns from the lookups used by countif.

Probably the simplest way to do that (if you want to stick with built-in functions) is to make the cells work on just the theme columns explicitly, with a formula like (in b2):

=countif($a$2:$a$9,a2)   countif($c$2:$c$9,a2)   countif($e$2:$e$9,a2)   countif($g$2:$g$9,c2)

I've only gone up to column g since I used your image as a test case, you'll obviously need to expand that to use all your columns, { a, c, e, g, i, k, m, o, q, s }.

Admittedly, that's a rather painful formula but you only need type it in once (in b2) then copy and paste to cells b3:b9, d2:d9, up to t2:t9.

Alternatively, you can use a combination of indirect, countif, and sum to achieve the same result with a shorter formula (again, expanding out to use all the individual column ranges up to s):

=sum(countif(indirect({"$a$2:$a$9","$c$2:$c$9","$e$2:$e$9","$g$2:$g$9"}),b2))

The next step beyond that is a user-defined function (UDF) that can do the heavy lifting for you. Opening up the VBA editor, you can create a module for your workbook (if one does not already exist), and enter the following UDF:

Function HowManyOf(lookFor, firstCell, lastCell, colSkip, rowSkip)
    ' What we are looking for.
    
    needVal = lookFor.Value
    
    ' Get cells.
    
    startCol = firstCell.Column
    startRow = firstCell.Row
    endCol = lastCell.Column
    endRow = lastCell.Row
    
    ' Ensure top left to bottom right, and sane skips.
    
    If startCol > endCol Then
        temp = startCol
        startCol = endCol
        endCol = temp
    End If
    
    If startRow > endRow Then
        temp = startRow
        startRow = endRow
        endRow = temp
    End If
    
    If colSkip < 0 Then colSkip = -colSkip
    If colSkip = 0 Then colSkip = 1
    
    If rowSkip < 0 Then rowSkip = -rowSkip
    If rowSkip = 0 Then rowSkip = 1
    
    ' Process each column.
    
    HowManyOf = 0
    
    For thisCol = startCol To endCol Step colSkip
        ' Process row within column.
        
        For thisRow = startRow To endRow Step rowSkip
            If Cells(thisRow, thisCol).Value = needVal Then
                HowManyOf = HowManyOf   1
            End If
        Next
    Next
End Function

Then you can simply enter the formula (again, start in b2):

' Args are:
'    The cell with the thing you want to count.
'    One corner of the range.
'    The opposite corner of the range.
'    Column skip.
'    Row skip.
' Corners can be any corner as long as they're opposite.
' Protected against negative and zero skips.

=howmanyof(a2, $a$2, $h$9, 2, 1)

Then, copying that formula into all the other cells will give you what you want: