I am trying to evaluate some information from an animal studying exercise using Google Sheets.

I'd like to know how many times the animal:

  • forages then allogrooms
  • sleeps then allogrooms


Also what the animal is likely to do after allogrooming. (Allogrooming is when it grooms one of its peers.)

I need to input another 9-hours of observation, so to work this out manually is not an option.

If you look at my spreadsheet you know know what I mean, but basically, I need a formula that counts how many times all the activities come before allogrooming and how many times x activities come after allogrooming.

I've highlighted the area I need some help with yellow.

The document is a copy and is editable.

sample spreadsheet

  • 1
    A link to the sheet is great, but we like questions to be self-contained. In other words, a potential answerer should have everything they need in the question; links and images should be supplemental. Worse, when this link stops working, this question becomes useless, and that is anathema to what Stack Exchange is trying to do. Can you add some simple sample data to your question, as well as the desired output? – ale Aug 9 '18 at 23:22

by adding and hiding 3 columns (F, G, H) it can be done: https://docs.ggl.com/spreadsheets/d/

how it works:

  • cell F3:
=ArrayFormula(IF(D3:D  "",
     IF(D3:D = $I$16,0,
     IF(D3:D = $I$17,3,
     IF(D3:D = $I$18,5,
     IF(D3:D = $I$19,7,
     IF(D3:D = $I$20,9,
     IF(D3:D = $I$21,11,
     IF(D3:D = $I$22,13,
     IF(D3:D = $I$23,15,
     IF(D3:D = $I$24,17,
     IF(D3:D = $I$25,19,
  • cell G3: =IF(AND(F3<>"",F4<>"",F3<F4), SUM(F3:F4),)

  • cell H3: =IF(AND(F3<>"",F4<>"",F3>F4), SUM(F3:F4),)

  • cell J17: =COUNTIF($G$3:$G,"=3")

F3 checks if column D has data and if yes then it creates unique value for every unique possibility. then formulas in G column checks if there is data for summing in F column and sums values only if they are "Preceded". same for formulas in H column, but sums values only if they are "Followed". and finally J17 simply checks only for unique sums and counts for the occurrences


I've built another way of solving this. It is on your demo file, on a sheet called 'Alternative'. The logic is very simple:

  • Add 'Preceding Action' (column E) and 'Following Action' (column F) to your data
  • Then for each of the UNIQUE list of actions you've created (on column H), use a simple


Add two columns to your data:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.