Count if cell row number is divisible by 9 and if the cell also does not say student.

[lars1565]

I want excel to check a column and countif the any cell in the column meets two criteria.

1) the cells row number needs to be divisible by 9, I think that would look something like MOD(ROW(A:A),9)=0

2) The cell also needs to not be blank or say student, something like AND(A:A<>"",A:A<>*Student*)

I feel (if it is actually possible) to something like:

=COUNTIFS(A:A,MOD(ROW(A:A),9)=0,AND(A:A<>"",A:A<>*Student*)

but not correct.

Any help is appreciated. Thanks
LL

 

[arthurbr]

Almost there =SUMPRODUCT(MOD(ROW(A:A),9)=0,A:A<>"",A:A<>"Student")

Using entire columns slows things down. better use a limited range or a dynamic one

 

[Marcelo Branco]

Assuming your data, say, in A2:A100 try

=SUMPRODUCT(--(MOD(ROW(A2:A100),9)=0),--(A2:A100<>""),--(A2:A100<>"Student"))

Remark: for the sake of performance do not use references to entire columns (like A:A) in SUMPRODUCT.

M.

 

[lars1565]

Brilliant, Thank you

 

[Aladin Akyurek]

Try to robustify though...

=SUMPRODUCT(--(MOD(ROW(A2:A100)-ROW(A2),9)=0),--(A2:A100<>""),--(A2:A100<>"Student"))

 

[Marcelo Branco]

Brilliant, Thank you

You are welcome. Glad to help

M.