|
Posted to microsoft.public.excel.misc
|
|
|
Counting criteria
Thanks Max, worked perfectly!
"Max" wrote:
Maybe you could give this tinker a shot ..
Source data assumed in A1:C15
In D1: =IF(AND(A1="FD",B1-C1=0),"x",IF(AND(A1="FD",B1-C1<0),"R",""))
Copy down to last row of source data, ie to D15
In E1: =COUNTIF(D$1:D1,"x")
Copy down to penultimate row of source data, ie to E14
Then place in say, F1, array-entered (press CTRL+SHIFT+ENTER):
=MAX(IF(D1:D15="x",E1:E15))-MAX(IF(D1:D15="R",E1:E15))
to yield the desired result
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andrew" wrote:
Hi Max, the formula doesn't work as it returned a 7 as opposed to 4. If you
notice in the example table, there are 2 sequences of FD in =0 mode. The
formula is supposed to check if a second (or third or onwards as the rows are
updated) sequence happened, and then return the value of the latest sequence.
In the table below, it should return 4 (FD being =0 4 times in the 2nd
sequence as opposed to the earlier sequence of 3 where FD =0)
Possible?
|