# Count if formula

#### Brew

##### Well-known Member
How do I create a formula that counts the N and the following Y in the list and if not then x.

example
T4:T13 = N,N,N,N,N,N,N,Y,Y,Y
formula Result
U4:U13 = 1,2,3,4,5,6,7,8,9,x,x

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Is this what you mean?

U4, copied down:

=IF(OR(T4={"N","Y"}),SUM(COUNTIF(T\$4:T4,{"N","Y"})),"X")

Does this work?
=IF(AND(T3="N",T4="Y"),COUNTA(\$T\$4:T4),IF(T4="N",COUNTA(\$T\$4:T4),"X"))

Dufus

Domenic, your formula, just does a continous numerical count of all records.
Dufus, your formula counts correctly, however, after x, when another N record appears, then recount. see expanded example.

example
T4:T15 = N,N,N,N,N,N,N,Y,Y,Y,N,N
formula Result
U4:U15 = 1,2,3,4,5,6,7,8,9,x,x,1,2
thanks

I need the formula to do execute like the following example:
example
T4:T18 = N,N,N,N,N,N,N,Y,Y,Y,N,N,Y,Y,N
formula Result
U4:U18 = 1,2,3,4,5,6,7,8,9,x,x,1,2,3,x,1

Does anyone know if code would be more efficient for this problem

Does this help ?

U4 =IF(T4="Y","x",IF((T4="N")*(T3="Y"),1,U3+1))

Regards
Bosco

thanks bosco_yip, i gonna go with this approach.

Replies
12
Views
264
Replies
2
Views
124
Replies
4
Views
43
Replies
17
Views
92
Replies
9
Views
263

1,214,769
Messages
6,121,434
Members
449,032
Latest member
egspen2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back