Counting Combination Pairs in Excel

3
votes

Alright, I have a spreadsheet that looks like this in the "B" column:

  1. AAA
  2. BBB
  3. BBB
  4. AAA
  5. BBB
  6. CCC
  7. DDD
  8. BBB
  9. AAA
  10. BBB
  11. CCC

What I need to do is to count how many times "BBB" directly follows "AAA" (In this example 3 times)

Ive tried multiple things with =SumProduct like =SUMPRODUCT(COUNTIFS(K6:K10,{"AAA","BBB"})) but that returns the product of all "AAA's" and "BBB's" instead of just the number of AAA & BBB pairs. The best I could do is to use =countifs() but I cant see a way to do a forward lookup like =countifs("B:B","AAA","B+1:B+1","BBB")

Also, I should mention, I was hoping to use this somewhere in the formula "Table13[[#All],[Radio State]]." That way the formula would grow and shrink depending on the size of the table.

Does anyone happen to know if its possible to do this?

Thanks Guys,

1
excelexcel-2007excel-formulaexcel-2010countif

1 Answers

4
votes

You can 'offset' the range by a bit like this:

=COUNTIFS(A1:A10, "AAA", A2:A11, "BBB")

You can change the range accordingly.

With a table:

=COUNTIFS(Table13[[#All],[Radio State]],"AAA",OFFSET(Table13[[#All],[Radio State]],1,0),"BBB")