- Joined
- Jul 14, 2012
- Messages
- 2
- Reaction score
- 0
Hi,
I'm trying to make a formula that counts cells that meet a certain criteria if the previous cell in the row also meets a criteria.
I started with the COUNTIF formula
=COUNTIF(B8:AF8,"T"), which counts the values in a row, but now I only want to count the cells with "T" where the previous cell in the row equals "O"
I worked out that the OFFSET formula works in order to reference the previous cell i.e.
=OFFSET(B8,0,-1)
I was think of combining the two with an AND command i.e.
=COUNTIF(AND(B8:AF8,"T",OFFSET((B8:AF8,0,-1)="O"))
but it doesn't work.
I could run a nested IF command
=IF(B8="T",IF(A8="O",0,1),"") for each cell
and then SUM up the cells, but I would need to have hidden rows or a hidden sheet to do the calculation, hence I'd like to us a formula in the single cell
I hope I've explained it clearly enough and that one of you knows a away to do what I'm trying to achieve.
]Thanks in advance
I'm trying to make a formula that counts cells that meet a certain criteria if the previous cell in the row also meets a criteria.
I started with the COUNTIF formula
=COUNTIF(B8:AF8,"T"), which counts the values in a row, but now I only want to count the cells with "T" where the previous cell in the row equals "O"
I worked out that the OFFSET formula works in order to reference the previous cell i.e.
=OFFSET(B8,0,-1)
I was think of combining the two with an AND command i.e.
=COUNTIF(AND(B8:AF8,"T",OFFSET((B8:AF8,0,-1)="O"))
but it doesn't work.
I could run a nested IF command
=IF(B8="T",IF(A8="O",0,1),"") for each cell
and then SUM up the cells, but I would need to have hidden rows or a hidden sheet to do the calculation, hence I'd like to us a formula in the single cell
I hope I've explained it clearly enough and that one of you knows a away to do what I'm trying to achieve.
]Thanks in advance