Count how many times 3 or 4 is observed in a sequence: Tracking Bees

[Rwtas]

Hi,

I am new to the forum and was just hoping to get some help analysing my experimental results.

I have attached an example spreadsheet of the data.

Basically the data is generated by tracking bees using antennas. The antennas are numbered 1,2,3 or 4 (Column C).

I need to count how many times 3 or 4 occur in a sequence. eg the count of (3,4,3,4,3) would equal 5.
Antenna 1+2 can be ignored.

Time Bee ID Antenna
20170413T082042.394418+10 14 3 (eg.Either 3 or 4 occurred 5 times a row)
20170413T082042.394418+10 14 4
20170413T082042.660167+10 14 3
20170413T082042.660167+10 14 4
20170413T082042.919958+10 14 3

Any help would be greatly appreciated.

Cheers

[leelnich]

Try:
=COUNTIF(C2:C54,">=3")

If you want to guard against higher numbered antennae fouling the count, use this:
=COUNTIFS(C2:C54,">=3",C2:C54,"<=4")

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[Rwtas]

Thanks for your reply. This doesn't really work as it only provides a total count of 3 and 4s for the entire data set.

Is there a way to say If Column C is >=3, it is a '1' ?

Then in the next column I could just sum all of the 1's to give me my answer.

[FlameRetired]

Not sure I understand. Try array entering this formula and fill down until you get 0s. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

I get this with your data:
17
10
9

[Rwtas]

That works perfectly, thanks for your help!

[FlameRetired]

You're welcome. Thanks for the feedback.

[Rwtas]

I've just come back to using the formula provided by FlameRetired (above).
However after a couple of months I can't get it to work properly (the only numbers it provides me with are 29 and 7).

I was just wondering if someone could help me use the formula in the spreadsheet attached.

Cheers

[FlameRetired]

Hi Rwtas.

You'll need to change the range references to include the increased data. It is still array entered and returns counts down to about row 265 starting with

1062
466
348
197
117

=LARGE(FREQUENCY(IF(($C$2:$C$14596>=3)*($C$2:$C$14596<=4),ROW($A$2:$A$14596)),
IF(NOT(($C$2:$C$14596>=3)*($C$2:$C$14596<=4)),ROW($A$2:$A$14596))),ROWS($2:2))