count formula 3s&1s

[andrichara]

hi to all, i have an arrey formula at H1 which counts 3s and 1s only from a row G:G(random generator of 3s,2s,1s) and in case of number 2 apeared should count 0 and then continue counting 3s and 1s.
the formula is working until 2 apears ,it shows 0zero but it stops, imean the zero is permanent,i want the formula to continue counting.


=IF(COUNTIFS(G:G,"<>1",G:G,"<>3",G:G,"<>")<>0,0,IFERROR(INT(MATCH(0,INDEX(--($G2:INDEX(G:G,COUNT(G:G))<>$G1:INDEX(G:G,COUNT(G:G)-1)),N(IF(1,COUNT(G:G)-ROW(INDIRECT("1:"&COUNT(G:G)-1))))),)/2),INT(COUNT(G:G)/2)))



i need your help please!!thanks

 

[Scott Huish]

Are you just counting 1s and 3s because that is a lot of formula for just that.

Excel 2010
	G	H
1	3	3
2	2
3	1
4	3
Sheet1

Cell Formulas
Range	Formula
H1	=SUM(COUNTIF(G:G,{1,3}))

 

[Scott Huish]

If you mean a running count, then try this:

=SUM(COUNTIF($G$1:G1,{1,3}))*(G1<>2)
Copy down.

Excel 2010
	G	H
1	3	1
2	2	0
3	1	2
4	3	3
Sheet1

Cell Formulas
Range	Formula
H1	=SUM(COUNTIF($G$1:G1,{1,3}))*(G1<>2)
H2	=SUM(COUNTIF($G$1:G2,{1,3}))*(G2<>2)
H3	=SUM(COUNTIF($G$1:G3,{1,3}))*(G3<>2)
H4	=SUM(COUNTIF($G$1:G4,{1,3}))*(G4<>2)

 

[andrichara]

If you mean a running count, then try this:

=SUM(COUNTIF($G$1:G1,{1,3}))*(G1<>2)
Copy down.

Excel 2010

	G	H

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]=SUM(COUNTIF($G$1:G1,{1,3}))*(G1<>2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=SUM(COUNTIF($G$1:G2,{1,3}))*(G2<>2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=SUM(COUNTIF($G$1:G3,{1,3}))*(G3<>2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=SUM(COUNTIF($G$1:G4,{1,3}))*(G4<>2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

thank you so much

the formula is counting an event ,like if 31 count1 if then 313count 1 if then3131 count 2 and so on,what i need is if any other number apeared except 3 and 1 to count 0 and if then continue if 3 1 apeared

 

[Scott Huish]

So did this work for you?

 

[Fluff]

Cross posted https://www.excelforum.com/excel-general/1262572-count-if-formula.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[andrichara]

So did this work for you?

thanks Huish!!super! is what i needed!sorry for my delay reply