Home > Blockchain >  Finding Countif Excluding some columns
Finding Countif Excluding some columns

Time:03-26

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...

CodePudding user response:

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

CodePudding user response:

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))
  • Related