Counting criteria

[andrew]

A B C
FD 1 1
EA 1 1
FD 2 2
FD 2 1
EA 2 1
FD 1 2
EA 1 2
EA 2 2
FD 2 1
FD 1 1
EA 2 1
FD 2 2
FD 3 2
EA 2 2
FD 1 2

Based on the table above, is there a formula to count the number of times
"FD" has a positive and/or zero total in sequence? (i.e. when formula detects
the text "FD", it calculates column B - column C = + or 0 total)

The table is updated 2 times a week so the row will keep adding. In the
above table example, the formula should return 4. This is because:
a) FD appeared 3 times with + or 0 result (row 1,3 & 4 before a break with
-result in subsequent FD appearance), then;
b) FD appeared 4 times again with + or 0 result (row 9, 10, 12 & 13 before a
break again).

What is the best way to calculate this?

 

[Max]

Something like this in say, E1:
=SUMPRODUCT((A1:A100="FD")*(B1:B100-C1:C100>=0))
should return what you seek

 

[andrew]

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?

 

[Max]

Ah, I mis-understood it earlier, sorry. Think I'm out of ideas.
Perhaps other responders could step in here ..

 

[Monkey-See, Monkey-Do]

why not have (in column d)

=IF(A1="FD", IF((B1-C1>=0), 1, 0), 0)

Which would put a 1 in the cell if your criteria is met and 0 if not...

Then at the bottom sum up all your column D? The total is the answer you
want...

 

[Max]

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$11,"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(D115="x",E1:E15))-MAX(IF(D115="R",E1:E15))
to yield the desired result

 

[Herbert Seidenberg]

Or this array formula:

=LOOKUP(999,IF(FREQUENCY(IF((bin2-bin3>=0)*(bin1="FD"),ROW(bin1)),
IF(bin2<bin3,ROW(bin1)))>0,
FREQUENCY(IF((bin2-bin3>=0)*(bin1="FD"),ROW(bin1)),
IF(bin2<bin3,ROW(bin1)))))

Variation on a theme by Biff.

 

[Max]

Herbert,
Besides the OP, I wonder just how many readers out there understand / know
how to implement your suggestion. Perhaps the inclusion of some explanation
lines on assumed sheet set-ups, ranges to be defined etc would certainly
help to support it, for completeness.

---

 

[andrew]

Thanks Max, worked perfectly!

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$11,"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(D115="x",E1:E15))-MAX(IF(D115="R",E1:E15))
to yield the desired result

 

[andrew]

Thanks Janusz.

 

[Max]

Glad to hear that. Do spare a moment to click the "Yes" button below, from
where you're reading this.

 

[Lori]

For an array formula slightly shorter than Herbert's above, maybe try this:

=1/LOOKUP(2,1/FREQUENCY(IF((A1:A15="FD")*(B1:B15-C1:C15>=0),
ROW(A1:A15)),IF((A1:A15="FD")*(B1:B15-C1:C15<0),ROW(A1:A15))))

Ctrl+Shift+Entered.

 

[Max]

David,
Out of politeness to Herbert, I was merely following his style of responding

 

[andrew]

Thanks Lori.

Is it also possible to use your formula to keep track whenever "FD" appears
in the weekly update? I mean, if "FD" appears in A15 but the result was a -
(negative), it will just show 0. If the following 2 weeks, A16 and A17 is
added where "FD" appears as + (positive), the formula counts it as 2. Can
this be done?