# Count how many times a certain word comes before and after another in a list

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

etc.

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.

• 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

`=countifs(D:D,"Allogrooming",E:E,\$H20)`