Search code examples
libreoffice-calc

Using COUNTIF for non-adjacent cells


I have a table listing various statistics for a given category over a number of years. Below is an example:

         |          2016         |          2017         |
Category | Stat1 | Stat2 | Stat3 | Stat1 | Stat2 | Stat3 |
   Cat1  |   2   |   4   |   4   |   6   |   3   |   3   |

I want to analyze each statistic over time. Specifically, I want to count the number of years each statistic was greater than a given value. Now, I know I can use COUNTIF for a cell range, but in my table the pertinent cells are not adjacent. I also know I could rearrange the table, but I'd like to avoid that if I could. Is it possible to execute a similar function over a number of non-adjacent cells?

Thank you.


Solution

  • A series (pair in the example) of COUNTIFs might be added together but perhaps easier with COUNTIFS. For example, with cutoffs chosen in M3:M9 and the stats named in N2:P2:

    =COUNTIFS($B$3:$G$3,">"&$M3,$B$2:$G$2,N$2)
    

    copied across and down to suit.

    COUNTIFS table example