CODEWITHSUNDEEP
vbaexcelexcel-2010countif
user1707493
user1707493

Reputation: 65

CountIf in Excel with formula in criteria

I'm trying to use a CountIf in Excel (2010) with a formula as criteria. Something like this:

=CountIf(rawdata!$AK:$AK;bitAnd(rawdata!$AK:$AK;F$3))

whereas BitAnd is a user-defined VBA function for a bitwise AND-operation:

Public Function bitAnd(a As Integer, b As Integer) As Integer
bitAnd = a And b
End Function

The task is to count all rows that have a certain bit flag set. For example, I'd like to count all rows that have its LSB set to 1 (such as 0001, 0101,...). That is, to do something like this (in pseudo-code):

IF bitAnd(1;any number in the range) == 1 THEN count

Obviously this doesn't work with CountIf, but is there any other elegant solution using formulas (instead of coding custom functions in VBA)? Any suggestions?

Upvotes: 2

Views: 1594

Answers (1)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15641

Use something like

=SUMPRODUCT(MOD(INT(($A3:$A19)/F$2),2)*1)

Here, $A3:$A19 is the source range to test, F2 is =2^(F$1-1), and F1 is the order of the bit you want to extract (1 is the LSB, etc.) Of course, you could write a straight formula =SUMPRODUCT(MOD(INT(($A3:$A19)/2^(F$1-1),2))*1). You should adapt absolute/relative indexing to your needs, to be able to conveniently copy-and-paste (as I did for testing the formulas).

Note that this is useful for extracting a single bit, as you were asking for. A "complete" bitwise AND might be constructed on this basis (although there are likely better options).

For the specific case of Excel 2013 (as of now, not your target), using the BITAND worksheet function along with COUNTIF or SUMPRODUCT should put you on track, and here you have a "complete" bitwise AND available (I do not have Excel 2013 to test this).

Upvotes: 1

Related Questions