Count IF formula help

[chuttus]

Hi,

In the attached sheet I want to enter a CountIF formula based on entries in Column A, B, C...

- In Column F, return the count of ID (only first entry) by month
- In Column G, return the count of PASS (only first entry) by month
- In Column H, return the count of FAIL(only first entry) by month

Thanks much all.

[63falcondude]

What do you mean by "only first entry"? If you were counting only the first entry, the count would be 1.

If you mean to count the number of unique ID's in each month, you can use this in F2:

=SUM(--(FREQUENCY(IF(TEXT(A$2:A$18,"mmmm")=E2,MATCH(B$2:B$18,B$2:B$18,0)),ROW(B$2:B$18)-ROW(B2)+1)>0)) Ctrl Shift Enter

Your conditional formatting and manually entered values (in column F) are looking at duplicates regardless of the month that they are in.

A more appropriate conditional formatting can be applied like this:

Highlight B2:B18 > Conditional Formatting > New Rule > Use a formula
=COUNTIFS(A:A,">"&EOMONTH(A2,-1),A:A,"<="&EOMONTH(A2,0),B:B,B2)>1
Format: Fill color of your choice > OK > OK

I don't understand how you came to the numbers that are in columns G and H.

Also, keep in mind that you do not have any years in column E so this will work reliably when there is only one year in column A (like in your sample).

[63falcondude]

I'll assume the manually entered values were mistakes.

You can use a helper column, let's say in column M using this formula in M2:
=COUNTIFS(A$2:A2,">"&EOMONTH(A2,-1),A$2:A2,"<="&EOMONTH(A2,0),B$2:B2,B2)

Then you can use this formula in G2:
=SUMPRODUCT((TEXT($A$2:$A$18,"mmmm")=$E2)*($C$2:$C$18=G$1)*($M$2:$M$18=1))
and drag it over and down.

[maras_mak]

My suggestion is VBA - Your last attachment.

Sub CountIF_()
    Dim a(), mth()
    Dim i As Integer, j As Integer, lr As Integer
    Dim d As Object
    
    Set d = CreateObject("scripting.dictionary")
    With ThisWorkbook.Sheets("Sheet1")
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        a = .Range("A2:C" & lr).Value
        lr = .Cells(Rows.Count, "E").End(xlUp).Row
        .Range("E2:H" & lr).Offset(0, 1).ClearContents
        mth = .Range("E2:H" & lr).Value
        For j = 1 To UBound(mth)
            For i = 1 To UBound(a)
                If Month(a(i, 1)) = Month(mth(j, 1)) And Year(a(i, 1)) = Year(mth(j, 1)) Then
                    If Not d.exists(a(i, 2)) Then
                        d(a(i, 2)) = a(i, 3)
                        mth(j, 2) = mth(j, 2) + 1
                        mth(j, 3) = mth(j, 3) + IIf(UCase(a(i, 3)) = "PASS", 1, 0)
                        mth(j, 4) = mth(j, 4) + IIf(UCase(a(i, 3)) = "FAIL", 1, 0)
                    End If
                End If
            Next
        Next
        .[E2].Resize(UBound(mth), UBound(mth, 2)) = mth
    End With
    Set d = Nothing
End Sub

Greetings.

[chuttus]

So,

for Column F, I only want to count entries which has occurred the first time but if that entry is already present in the past date then it should not count it.
for Column G, count the number of PASS from Column C (disregard the respective ID, that is not counted due to previous entry)
for Column H, count the number of FAIL from Column C (disregard the respective ID, that is not counted due to previous entry)

Disregard the Conditional formatting, I just did it to help manual counting.
I have fixed the column E to include Month & Year

Thanks!!

[63falcondude]

My previous answers still apply. The only difference is accounting for the year now.

That is:

F2 =SUM(--(FREQUENCY(IF((A$2:A$18>=E2)*(A$2:A$18<=EOMONTH(E2,0)),MATCH(B$2:B$18,B$2:B$18,0)),ROW(B$2:B$18)-ROW(B2)+1)>0)) Ctrl Shift Enter
Drag down through F4.

(Helper column) M2 =COUNTIFS(A$2:A2,">"&EOMONTH(A2,-1),A$2:A2,"<="&EOMONTH(A2,0),B$2:B2,B2)
Drag down through M18.

G2 =SUMPRODUCT(($A$2:$A$18>EOMONTH($E2,-1))*(($A$2:$A$18<=EOMONTH($E2,0)))*($C$2:$C$18=G$1)*($M$2:$M$18=1))
Drag over to H2 then down through row 4.

[chuttus]

I tried using the formula, I see for the month of November the count is returning as '6' instead of '5'.
I have manually counted the entries in Column I to compare.

11/04/18 100697 (count)
11/15/18 100257A (count)
11/16/18 100546 (count)
11/20/18 100257A (do not count)
11/26/18 100602 (count)
11/27/18 100397B (count)
11/30/18 641881 (do not count)

[63falcondude]

Why should cell B14, 641881, not be counted for November?

[Bo_Ry]

Please try

F2
=SUMPRODUCT((TEXT($A$2:$A$18,"mmmm")=E2)*(MATCH($B$2:$B$18,$B$2:$B$18,)=ROW($A$2:$A$18)-ROW($A$1)))

G2 copy to H2
=SUMPRODUCT((TEXT($A$2:$A$18,"mmmm")=$E2)*(MATCH($B$2:$B$18,$B$2:$B$18,)=ROW($A$2:$A$18)-ROW($A$1))*($C$2:$C$18=G$1))

copy F2:H2 down

[chuttus]

@maras_mak - thanks for the VBA but I prefer solving using formula

@63falcondude - 641881 in Cell B14 is already entered in Cell B6, hence do not count

@Bo_Ry - I tried your formula, it worked for my initial posted file but then I changed the format of Column E from TEXT to DATE to capture both month and year. So now your formula is not giving me correct solution. See updated attached file.

[Bo_Ry]

Please try
F2
=SUMPRODUCT((TEXT($A$2:$A$18,"myy")=TEXT(E2,"myy"))*(MATCH($B$2:$B$18,$B$2:$B$18,)=ROW($A$2:$A$18)-ROW($A$1)))

G2 copy to H2
=SUMPRODUCT((TEXT($A$2:$A$18,"myy")=TEXT($E2,"myy"))*(MATCH($B$2:$B$18,$B$2:$B$18,)=ROW($A$2:$A$18)-ROW($A$1))*($C$2:$C$18=G$1))

[chuttus]

It worked fine, but can I extend this formula to not just 18 rows? Keep in mind data in Column A, B, C will be empty after row 18 for now but it will keep populating periodically. I tried 500 columns like below but gave me an error.

=SUMPRODUCT((TEXT($A$2:$A$500,"myy")=TEXT(E2,"myy"))*(MATCH($B$2:$B$500,$B$2:$B$500,)=ROW($A$2:$A$500)-ROW($A$1)))

[Bo_Ry]

Try this

F2
=SUMPRODUCT((TEXT($A$2:$A$2000,"myy")=TEXT($E2,"myy"))*(MATCH($B$2:$B$2000&"",INDEX($B$2:$B$2000&"",),)=ROW($A$2:$A$2000)-ROW($A$1)))

G2 copy to H2
=SUMPRODUCT((TEXT($A$2:$A$2000,"myy")=TEXT($E2,"myy"))*(MATCH($B$2:$B$2000&"",INDEX($B$2:$B$2000&"",),)=ROW($A$2:$A$2000)-ROW($A$1)*($C$2:$C$2000=G$1)))

[chuttus]

Worked great Bo_Ry, thanks a lot.