Trying to avoid helper column formula

[Gliffix101]

Hello All,

I have a data set where an Entry is graded Pass/Fail on a series of inspection points. If any of the inspection points "Fail", the Entry is considered a fail. I'm looking to write a formula that reviews each line and counts each Entry that is a Pass. I have been able to accomplish this so far by adding a helper column with the formula =IF(COUNTIF(A2:M2,"Fail"),"Fail","Pass") but my data set changes constantly, so I'd like to avoid using a helper column if at all possible. Formula need is the Question Mark below:

Data Set (Sheet1!):
[TABLE="width: 611"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Loan Number[/TD]
[TD]Question 1[/TD]
[TD]Question 2[/TD]
[TD]Question 3[/TD]
[TD]Helper Column[/TD]
[/TR]
[TR]
[TD]Entry 1[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]Entry 2[/TD]
[TD]Pass[/TD]
[TD]N/A[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]Entry 3[/TD]
[TD]Pass[/TD]
[TD]Fail[/TD]
[TD]Pass[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]Entry 4[/TD]
[TD]Fail[/TD]
[TD]Fail[/TD]
[TD]Fail[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]Entry 5[/TD]
[TD]Fail[/TD]
[TD]N/A[/TD]
[TD]Pass[/TD]
[TD]Fail[/TD]
[/TR]
</tbody>[/TABLE]

Master Sheet (Summary!):

Total Number of Entry: =SUM(Number of Entry passed + Number of Entry failed)
Number of Entry passed: ?
Number of Entry failed: ?


Thank you,

Bill

 

[Nine Zero]

Use this for the Entry passed formula change your columns with your own

=SUM(COUNTIFS(A:A,{"Pass","N/A"},B:B,{"Pass","N/A"},C:C,{"Pass","N/A"}))

EDIT: forgot to add the SUM portion should work either way though

 

[Gliffix101]

Thank you for the reply. That is going to give me the total counts of all Passes. I'm looking for a formula that will combine the Helper column formula and the Sum of the Helper column data.

Today, my data is the Loan Number, and then Question 1 thru 3. I have to manually add the helper column =IF(COUNTIF(A2:M2,"Fail"),"Fail","Pass") and then run a SUM on that =COUNTIF(N2:N2892,"Pass").

I was looking for one formula that would look at each row, mark it Pass/Fail based on the criteria, and then SUM all of the passes. The more I research, the more likely it seems that I must have the helper column and can't accomplish this through a formula. The helper column is easier, but it's more manual and eliminates any chance of putting this into production without manual implementation.

Bill

 

[mse330]

Hey Gliffix101,

I am not sure if that could be achieved in an array formula (still learning them ) but until someone figures it out this is a UDF (User Defined Function) that should achieve what you are looking for. The UDF has 2 arguments, the 1st as the range of data & the 2nd is optional which is the word "Pass" or "Fail" - I said it's optional because if left blank, it will show the number of "Pass".

To install the UDF, you need to
1. Press Alt+F11
2. Press Alt+I+M
3. Paste the below code
4. Save the file in macro enable format (.xlsm or .xlsb)

Function Count_Pass_Fail(Rg As Range, Optional PassFail As String) As Long
Dim Arr() As Variant, Chk As Boolean
Arr = Rg
For x = LBound(Arr) To UBound(Arr)
    For y = LBound(Arr, 2) To UBound(Arr, 2)
        If Arr(x, y) = "Fail" Then Chk = True
    Next y
    If Chk = False Then Count_Pass_Fail = Count_Pass_Fail + 1
    Chk = False
Next x
If PassFail = "Fail" Then Count_Pass_Fail = UBound(Arr) - Count_Pass_Fail
End Function

Book1
	A	B	C	D	E	F	G
1	Loan Number	Question 1	Question 2	Question 3
2	Entry 1	Pass	Pass	Pass		Count Pass	2
3	Entry 2	Pass	N/A	Pass		Count Fail	3
4	Entry 3	Pass	Fail	Pass		Total	5
5	Entry 4	Fail	Fail	Fail
6	Entry 5	Fail	N/A	Pass
Sheet1

Cell Formulas
Range	Formula
G2	=Count_Pass_Fail($B$2:$D$6,"Pass")
G3	=Count_Pass_Fail($B$2:$D$6,"Fail")
G4	=SUM(G2:G3)

 

[Aladin Akyurek]

A1: Total Number of Entries

In B1 of Summary control+shift+enter, not just enter...

=SUM(IF(MMULT((Sheet1!B2:D6<>"")+0,TRANSPOSE(COLUMN(Sheet1!B2:D6)^0))>0,1))

A2: Number of Entries passed

In B2 just enter...

=B1-B3

A3: Number of Entries Failed​

In B3 control+shift+enter, not just enter...

=SUM(IF(MMULT((Sheet1!B2:D6="fail")+0,TRANSPOSE(COLUMN(Sheet1!B2:D6)^0))>0,1))

 

[bkjohn2016]

Hi Bill,

I always tell people that they can do alot with a simple sumif and vlookup. Sumproduct does both...try this:

=SUMPRODUCT(
(--(DataSheet!B:B=A3))
+(--(DataSheet!C:C=A3))
+(--(DataSheet!D:D=A3)))

Cell A3="Fail". This first formula counts the items labeled "Failed" in the data sheet.

=SUMPRODUCT(
(--ISTEXT(DataSheet!A:A)),
(--(DataSheet!B:B<>A3))+
(--(DataSheet!C:C<>A3))+
(--(DataSheet!D:D<>A3)))-3

This second calculation counts everything not labeled "Failed". And then just add the two together.

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Fail[/TD]
[TD="width: 64, align: right"]5[/TD]
[/TR]
[TR]
[TD]Pass[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

@bkjohn, the OP is Not looking for the Total of Fail and Not Fail/Pass, that could be accomplished by a simple COUNTIF or COUNTIFS.

OP needs the Count of how many "Entry" is Pass or Fail, for the sample OP provided the answer would be 2 for Pass, and 3 for Fail.

 

[Gliffix101]

mse330,

This thing worked like a champ!!! Thank you for posting. You just saved me hours with this!

Thanks,

Bill

 

[jtakw]

Check out Aladin's formulas in Post #5 , they also work.

 

[Gliffix101]

I tried Aladin's way, but it was giving me incorrect calculations back. I liked mse330's post because I can apply that same function to other tasks without the specificity the formula required for Aladin's post.

Bill