Countif Issue, Please help

[Sunilbsp]

I know there are many messages floating for "Countif". But i have a rare issue

This below is a format which i query from a webpage, in which the initials comes in the second line. I have 2 guys with the same name but the initials are different. No I need to Query how many "medium" tickets "Hemanth AC" and how many the other guy handled. I tried querying =COUNTIFS(Sheet2!G:G,"3-Medium",Sheet2!I:I,">24:00",Sheet2!H:H,"Hemanth") but it gives me a result of both persons. So i need to include the second row(It should take the initial as well), so that i get different results for both. Would really appreciate your help. Thanks in Advance

Ticket No	Impact	Name	Time
1111	Medium	Hemanth	22:00
		AC
2222	Medium	Hemanth	25:00:00
		AC
4444	Medium	Hemanth	2:00
		Kumar
5555	Low	Hemanth	4:00
		Kumar
6666	High	Hemanth	16:00
		AC

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[Sunilbsp]

Someone please help!!

 

[RonB1111]

Add a helper column (say Col E) - in E2, enter the formula =C3 and then copy down. Now you should be able to get countifs tp work

 

[Robert Mika]

I know there are many messages floating for "Countif". But i have a rare issue

This below is a format which i query from a webpage, in which the initials comes in the second line. I have 2 guys with the same name but the initials are different. No I need to Query how many "medium" tickets "Hemanth AC" and how many the other guy handled. I tried querying =COUNTIFS(Sheet2!G:G,"3-Medium",Sheet2!I:I,">24:00",Sheet2!H:H,"Hemanth") but it gives me a result of both persons. So i need to include the second row(It should take the initial as well), so that i get different results for both. Would really appreciate your help. Thanks in Advance

Ticket No	Impact	Name	Time
1111	Medium	Hemanth	22:00
		AC
2222	Medium	Hemanth	25:00:00
		AC
4444	Medium	Hemanth	2:00
		Kumar
5555	Low	Hemanth	4:00
		Kumar
6666	High	Hemanth	16:00
		AC

<tbody>
</tbody>

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Ticket No	Impact	Name	Time
2	1111	Medium	Hemanth	22:00			Medium	Hemanth	AC	2
3			AC				Medium	Hemanth	Kumar	1
4	2222	Medium	Hemanth	25:00:00
5			AC
6	4444	Medium	Hemanth	2:00
7			Kumar
8	5555	Low	Hemanth	4:00
9			Kumar
10	6666	High	Hemanth	16:00
11			AC
Sheet1

Cell Formulas
Range	Formula
J2	=COUNTIFS($B$2:$B$10,G2,$C$2:$C$10,H2,$C$3:$C$11,I2)
J3	=COUNTIFS($B$2:$B$10,G3,$C$2:$C$10,H3,$C$3:$C$11,I3)

 

[Michael M]

Try

Excel 2003
	A	B	C	D
1	Ticket No	Impact	Name	Time
2	1111	Medium	Hemanth AC	10:00:00 PM
3	2222	Medium	Hemanth AC	1:00:00 AM
4	4444	Medium	Hemanth Kumar	2:00:00 AM
5	5555	Low	Hemanth Kumar	4:00:00 AM
6	6666	High	Hemanth AC	4:00:00 PM
7
8			Count for Hemanth Kumar	1
9			Count for Hemanth AC	2
Sheet1

Cell Formulas
Range	Formula
D8	=SUMPRODUCT(--($B$2:$B$6="Medium")*($C$2:$C$6="Hemanth Kumar"))
D9	=SUMPRODUCT(--($B$2:$B$6="Medium")*($C$2:$C$6="Hemanth AC"))

 

[prince141987]

This Code will paste the Intial into same cell in which the name is appearing so just use this code and use the Count If with Criteria as u are already using with initial and the name

Sub CountRecord()
    
    Dim lngSum          As Long
    Dim rngData         As Range
    Dim lngRow          As Long
    
    Const strSheetname  As String = "Sheet1"
    Const strStartCell  As String = "A1"
    Const lngCol        As Long = 3
    
    With ThisWorkbook.Worksheets(strSheetname)
        Set rngData = Intersect(.Range(strStartCell).CurrentRegion.Offset(1), .Range(strStartCell).CurrentRegion)
        With rngData
            For lngRow = 1 To .Rows.Count Step 2
                .Columns(lngCol).Cells(lngRow, 1) = Trim(.Columns(lngCol).Cells(lngRow, 1) & " " & .Columns(lngCol).Cells(lngRow + 1, 1))
            Next
        End With
        .AutoFilter 1, ""
        .SpecialCells(xlCellTypeBlanks).Delete
        .AutoFilter
    End With
End Sub

Regards
Prince

 

[Sunilbsp]

@ Prince: Thanks..but do you think this will work for a live page which gets refreshed every min from a webpage?

And thank you so much rest of all. I will try working those and will let you know all whose solution works...

Regards,
Sunil

 

[Sunilbsp]

Robert. thanks ...but its not just these 2 names in this, i have other numbers also which might change since its querying from a web page. Do we have an option to attach a sheet here..if so i would have done that !!

 

[Robert Mika]

Robert. thanks ...but its not just these 2 names in this, i have other numbers also which might change since its querying from a web page. Do we have an option to attach a sheet here..if so i would have done that !!

See my signature on how to post data.

 

[Sunilbsp]

Micheal, thanks : But this is not in the same cell. Name and the initial are in different ones