If then and more

[JJP]

Good Morning Everyone,

I am in the process of creating an excel spread sheet that will be used to
track student tardiness. To this point I am using the following formula to
=COUNTIF(B2:F2,"N") which is working rather well.

1. Does this type of formula work well when the name of students changes from
day to day?

2. Also, would this formula track tardiness from sheet to sheet?

Please Advise

 

[Sean Timmons]

Presumably, student names are in B1 - F1 or B3 - F3?

Wouldn't matter in the leat.

I would change the formula to:

=COUNTIF(B:F,"N")

to ensure all records are found regardless of class size.

As to counting acrsoss sheets, you have two options..

If you have sheet names of say: Sheet1, Sheet2, Sheet3, etc, it's easier.

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet" &ROW(1:10) &"'!B:F"),"N"))

should get you there.

If the sheets have unique-type names, you'll have to use a helper column.

Say, column H, enter your sheet names down the column starting in row 1:

=SUMPRODUCT(COUNTIF(INDIRECT(H1:H10&"'!B:F"),"N"))