Finding Countif Excluding some columns

user_10693894 0
Viewed 60

my data ...

====================================================================
+   A  +  B  +  C  +  D  +  E  +  F  +  G  +  H  +  I  +  J  +  K  + L
====================================================================
+  10  +  3  +  5  +  0  +  0  +  0  +  7  +  8  +  9  +  10 + 50 + 20
====================================================================

i want to use CountIf function in A, D, G, J columns only. i wrote following formula.

=COUNTIF(A9:V9, ">0")/3

is there any alternate method ? plz...

excel-formula excel
2 Answers

=INDEX(FREQUENCY((A9,D9,G9,J9),0),2)

user_170077040

You could try:

=SUM((A9:L9>0)*(MOD(COLUMN(A9:L9),3)=1))

Swap SUM() with SUMPRODUCT() for Excel versions prior to ms365

Or, if available:

=SUM(--TAKE(WRAPROWS(A9:L9,3)>0,,1))

Or:

=SUM(--TAKE(WRAPCOLS(A9:K9,3)>0,1))
user_97581940
Related