All of us love COUNTIF. And it is very easy to do – just say =COUNTIF("A1:A100",">5") and it finds all the values within the range A1 to A100 which are greater than 5. But what if I wanted the result for only A3, A8 and it should omit other cells. Try putting in following formula –
=COUNTIF((A3, A8),">5") and it will give you #VALUE error.
A possible solution is
=(A3>5)+(A8>5)
What happens if you need to do for A3, A4, A5, A8, A24, A40, A45, A89. Now, you will have to use a formula like –
=(A3>5)+(A4>5)+(A5>5)+(A8>5)+(A24>5)+(A40>5)+(A45>5)+(A89>5)
The formula becomes cumbersome as the number of cells increase. In this case, you can use below formula. This single formula can take care of contiguous (like A3:A5) and non-contiguous ranges both –
=SUM(COUNTIF(INDIRECT({"A3:A5","A8","A24","A40","A45","A89"}),">5"))
Edit – 18-Aug-20
The comment by Ian has prompted me to come up with alternative formulas
=SUMPRODUCT(–(A3:A5>5))+SUMPRODUCT(–(CHOOSE({1,2,3,4,5},A8,A24,A40,A45,A89)>5))
=SUMPRODUCT(–(A3:A5>5))+SUMPRODUCT(–(N(OFFSET(A1,{7;23;39;44;88},))>5))
But when you drag this formula down the rows don't auto increase. Is there a workaround?
I have updated the post with alternative formulas so that when you drag it, it updates. You can put $ signs as per your need, if needed.
Can this trick be done with multiople criteria (C, F, S), say, a range (Activity_Participation)
)?
Oh, I'm trying to count the number of occurrences of any one of 3 criteria in a set of non-contiguous cells
e.g. if [Day 3] has C or F or S, if [Day 4] has C or F or S, if [Day 5] has C or F or S
And just to be a pain, the cells are in a table and the table is filtered