COUNTIF Function with variables

[TJ]

I have a spreadsheet that records the initials of a stage check airman in one
column, the next column can be marked with an X if the student fails the oral
portion, the next column marked with an X if they fail the flight portion. I
want to count the number of times the initials are found with no X's in
either column (which means the student passed the check) and also count the
number of times the initials are found with either column having an X in it.

Any suggestions?

 

[Per Jessen]

Hi

With initials and fails in columns A:C and a table of unique initials in
column H this formula will return the number of passed tests:

=SUMPRODUCT(($A$2:$A$100=H2)*1,($B$2:$B$100="")*1,($C$2:$C$100="")*1)

And this will return the number of faild tests. A test will count as one if
either or both tests in same row is failed:

=SUMPRODUCT(($A$2:$A$100=H2)*1,($B$2:$B$100="X")*1)+SUMPRODUCT(($A$2:$A$100=H2)*1,($C$2:$C$100="X")*1)-SUMPRODUCT(($A$2:$A$100=H2)*1,($B$2:$B$100="X")*1,($C$2:$C$100="X")*1)

The formula is to be entered as one line.

Regards,
Per

 

[Bob Phillips]

=SUMPRODUCT(--($A$2:$A$20="RP"),--(($B$2:$B$20="")+($C$2:$C$20="")=2))

and

=SUMPRODUCT(--($A$2:$A$20="RP"),--(SIGN(($B$2:$B$20="X")+($C$2:$C$20="X"))))

 

[TJ]

Thanks!

Lets say I want to complicate this one more step and seperate the check
instructor stats into "initial check" pass and fail and also "recheck pass
and fail"

Currently I have each students columns set up as A column "date", B column
"Check Instructor Initials", C column "failed oral", D Column "failed
flight". I have three rows per student with the initial check in row 1 and
space for 2 re-checks in row 2 and 3 if necessary.

The formula given to me by Bob works perfectly to evaluate all the checks
the same. I assume I will have to add a hidden column E that puts an X in row
1 that tells the formula that that is the "initial check".

Do I have to add an array to the formula? I dont care about seperating out
recheck 1 and 2. Those can be tallied under the same recheck column.

Thanks for the help!

 

[Bob Phillips]

If it is always batched in groups of 3, you should be able to tell the
formula to look at only every third row. Just add this test

--(MOD(ROW($A$2:$A$20),3)=0)

to the formula.

I would give you the whole formula, but your data layout seems to have
changed, and I cannot see where the student initials are now, so I am
confused by it.

 

[TJ]

Unfortunately, I cannot do the every third row modification as there is the
occasional bone-head that takes 4 attempts at the check and I have to
manually add a fourth row.

Sorry for the confusion on the layout. It isnt really laid out the way I
said, I was just leaving out what I thought was the extra's. Here is the
columns from left to right: Student Name, Student ID Number, Student Start
Date, Date of stage 1 check, initials of check instructor that administered
the check, a box to put an X in if they failed the oral, a box to put an X in
if they failed the flight and then the next column is the date of the stage 2
check and it continues on until column CC for all the checks.

Rows 1-3 are merged in columns A,B and C and they are available for data in
D,E,F and G. Row 4 starts again with a new student.

The formula I am writing is on a different tab designed to track the stats
of my check instructors. It is in this tab that I have set up columns of
"name of check administered", "first attempt passed", "first attempt failed,
"recheck passed" and "recheck failed. The rows would be stage 1 check, stage
2 check and so on.

The formula you wrote helps if I have only two columns of pass and fail. If
I add a column H that puts an X in row 1 and leaves rows 2 and 3 blank, is
there a way to re-write the formula to separate out checks and re-checks?

 

[TJ]

SUMPRODUCT(--($A$2:$A$4="MW"),--(($B$2:$B$4="")+($C$2:$C$4="")=2),--($D$2:$D$4="")
SUMPRODUCT(--($A$2:$A$4="MW"),--(($B$2:$B$4="")+($C$2:$C$4="")=2),--($D$2:$D$4="X")
SUMPRODUCT(--($A$2:$A$4="MW"),--(SIGN(($B$2:$B$4="X")+($C$2:$C$4="X"))),--($D$2:$D$4="")
SUMPRODUCT(--($A$2:$A$4="MW"),--(SIGN(($B$2:$B$4="X")+($C$2:$C$4="X"))),--($D$2:$D$4="X"))

I think I just answered my own question by adding a third array to
differentiate between a check and a recheck.

The first equation returns a 1 for a check passed on the first attempt
administered by MW
The second equation returns a 1 for all re-checks passed administered by MW
The third equation returns a 1 for a check failed on the first attempt
administered by MW
The fourth equation returns a 1 for a check failed on a recheck administered
by MW.