CountIf in Excel with formula in criteria

Question

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?

do the calculation (criteria) in column next to data... refer the countif criteria to those cells — Joop
– Joop, Commented Dec 12, 2013 at 13:23

sancho.s ReinstateMonicaCellio · Accepted Answer · 2013-12-12 14:47:55Z

1

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).

edited Dec 12, 2013 at 14:47

answered Dec 12, 2013 at 13:20

sancho.s ReinstateMonicaCellio

15.8k25 gold badges110 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1707493 Over a year ago

Hi, that's a nice approach, I didn't know that. However, it only works if there's just a ONE bit flag set to 1. In my case, it might as well happen that there are several bits set. That's why I tried to work with a bitwise AND. Do you have a suggestion how to deal with it?

sancho.s ReinstateMonicaCellio Over a year ago

@user1707493 - I do not understand what you mean. I tested this on all numbers n in the interval [0,17] (i.e., many of them have more than one bit set to 1), extracting bits b #1, #2 or #3, and it works. For example, n and 0010b. As said, it cannot compute n and 0110b, if that is what you mean. I would google something like "excel bitwise and" (I actually did that, tried a couple of options that I found, none working). That is probably worth another question, as it is interesting as a closed question on its own.

Collectives™ on Stack Overflow

CountIf in Excel with formula in criteria

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related