Countif Binary Strings w/ "1" in Position

[Excel18]

Hello,
I am having a problem with counting specific binary strings (with the value of "1" in a specific position) and was hoping for some advice.

I am importing data into Excel and converting each point to binary (I think this is the best way).

An example: Event 1 has the value 001. Event 3 has the value 100. When they occur simultaneously, the value is 101.

I am looking for a way to count each time an event occurs, either solo or simultaneously with other events. So, in the above example, I want to count whenever Event 1 occurs (and the string appears ??1).

I was hoping to create an equation which counted whenever the third digit in the string equals 1. I was thinking of using an equation such as

COUNTIF(Range,MID(Cell,3,1)=1)

or

COUNTIF(Cell,"??1")

​

However, neither of these work and I am unsure of how to proceed. Does anyone have any advice?
Thanks.

 

[JoeMo]

Try:

=SUMPRODUCT(--(RIGHT(Range,1)="1"))

where Range is the address of the range you want to count (e.g. A1:A100).

 

[jtakw]

Hi,

For some reason, a simple COUNTIF works for me whether the cell is formatted as Custom 000, or Text:

Excel 2010
	A	B	C	D	E	F	G
1	001	Count of			001	Count of
2	100	001	3		100	001	3
3	001	010	4		001	010	4
4	010	100	3		010	100	3
5	010				010
6	100				100
7	010				010
8	100				100
9	010				010
10	001				001
11
12	Formatted Custom 000				Formatted as Text
13	TRUE				FALSE
Sheet1

Cell Formulas
Range	Formula
C2	=COUNTIF(A$1:A$10,1)
C3	=COUNTIF(A$1:A$10,10)
C4	=COUNTIF(A$1:A$10,100)
G2	=COUNTIF(E$1:E$10,1)
G3	=COUNTIF(E$1:E$10,10)
G4	=COUNTIF(E$1:E$10,100)
A13	=ISNUMBER(A1)
E13	=ISNUMBER(E1)

 

[Scott Huish]

For the COUNTIF solution, you don't have any mixed events, such as 101.

 

[jtakw]

For the COUNTIF solution, you don't have any mixed events, such as 101.

Here, not sure why it works (for the Text column):

Excel 2010
	A	B	C	D	E	F	G
1	001	Count of			001	Count of
2	100	001	2		100	001	2
3	001	010	3		001	010	3
4	111	100	2		111	100	2
5	010	101	1		010	101	1
6	101	111	2		101	111	2
7	010				010
8	100				100
9	010				010
10	111				111
11
12	Formatted Custom 000				Formatted as Text
13	TRUE				FALSE
Sheet1

Cell Formulas
Range	Formula
C2	=COUNTIF(A$1:A$10,1)
C3	=COUNTIF(A$1:A$10,10)
C4	=COUNTIF(A$1:A$10,100)
C5	=COUNTIF(A$1:A$10,101)
C6	=COUNTIF(A$1:A$10,111)
G2	=COUNTIF(E$1:E$10,1)
G3	=COUNTIF(E$1:E$10,10)
G4	=COUNTIF(E$1:E$10,100)
G5	=COUNTIF(E$1:E$10,101)
G6	=COUNTIF(E$1:E$10,111)
A13	=ISNUMBER(A1)
E13	=ISNUMBER(E1)

 

[Aladin Akyurek]

Hello,
I am having a problem with counting specific binary strings (with the value of "1" in a specific position) and was hoping for some advice.

I am importing data into Excel and converting each point to binary (I think this is the best way).

An example: Event 1 has the value 001. Event 3 has the value 100. When they occur simultaneously, the value is 101.

I am looking for a way to count each time an event occurs, either solo or simultaneously with other events. So, in the above example, I want to count whenever Event 1 occurs (and the string appears ??1).

I was hoping to create an equation which counted whenever the third digit in the string equals 1. I was thinking of using an equation such as

COUNTIF(Range,MID(Cell,3,1)=1)

or

COUNTIF(Cell,"??1")

​

However, neither of these work and I am unsure of how to proceed. Does anyone have any advice?
Thanks.

Maybe you should proceed to create a sample along with the specs what to count and the associated count.

It's important to know the nature of your input. For example:

'001

in A2 is text, that is, =ISNUMBER(A2) >> FALSE.

If A2 houses 1 and =ISNUMBER(A2) >> TRUE, formatting A2 to display 001 doesn't make A2 text.

 

[Scott Huish]

Here, not sure why it works (for the Text column):

The op wanted to count when say event 1 occurred, so for 001 and 101, it should count 2 for this because 1 is in the rightmost column both times, not as 2 different results but 1.