Countif Issue, Please help

S

Sunilbsp

New Member
Joined
Apr 18, 2013
Messages
8
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

[TABLE="width: 257"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Ticket No[/TD]
[TD]Impact[/TD]
[TD]Name[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD="align: right"]1111[/TD]
[TD]Medium[/TD]
[TD]Hemanth[/TD]
[TD="align: right"]22:00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]AC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2222[/TD]
[TD]Medium[/TD]
[TD]Hemanth[/TD]
[TD="align: right"]25:00:00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]AC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4444[/TD]
[TD]Medium[/TD]
[TD]Hemanth[/TD]
[TD="align: right"]2:00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Kumar[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5555[/TD]
[TD]Low[/TD]
[TD]Hemanth[/TD]
[TD="align: right"]4:00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Kumar[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6666[/TD]
[TD]High[/TD]
[TD]Hemanth[/TD]
[TD="align: right"]16:00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]AC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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
 
Upvote 0
Sunilbsp said:
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

[TABLE="width: 257"]
<tbody>[TR]
[TD]Ticket No[/TD]
[TD]Impact[/TD]
[TD]Name[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD="align: right"]1111[/TD]
[TD]Medium[/TD]
[TD]Hemanth[/TD]
[TD="align: right"]22:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222[/TD]
[TD]Medium[/TD]
[TD]Hemanth[/TD]
[TD="align: right"]25:00:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4444[/TD]
[TD]Medium[/TD]
[TD]Hemanth[/TD]
[TD="align: right"]2:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Kumar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5555[/TD]
[TD]Low[/TD]
[TD]Hemanth[/TD]
[TD="align: right"]4:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Kumar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6666[/TD]
[TD]High[/TD]
[TD]Hemanth[/TD]
[TD="align: right"]16:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Excel 2010
ABCDEFGHIJ
1Ticket NoImpactNameTime
21111MediumHemanth22:00MediumHemanthAC2
3ACMediumHemanthKumar1
42222MediumHemanth25:00:00
5AC
64444MediumHemanth2:00
7Kumar
85555LowHemanth4:00
9Kumar
106666HighHemanth16:00
11AC
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0
Try



Excel 2003
ABCD
1Ticket NoImpactNameTime
21111MediumHemanth AC10:00:00 PM
32222MediumHemanth AC1:00:00 AM
44444MediumHemanth Kumar2:00:00 AM
55555LowHemanth Kumar4:00:00 AM
66666HighHemanth AC4:00:00 PM
7
8Count for Hemanth Kumar1
9Count for Hemanth AC2
Sheet1
Cell Formulas
RangeFormula
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"))
 
Upvote 0
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:)

Code: 
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
 
Upvote 0
@ 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
 
Upvote 0
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 !!
 
Upvote 0
Sunilbsp said:
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 !!
Click to expand...

See my signature on how to post data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
VBA CountIf function results in "0"
Replies
0
Views
1K
PlutoQX
P
B
  • Question Question
COUNTIFS with Date Criteria Help
Replies
1
Views
641
jorismoerings
J
H
  • Question Question
Distinct Count based on given criteria
Replies
3
Views
832
SpillerBD
S
B
  • Question Question
Calculate consecutive blank cell for each day - Attendance sheet
Replies
3
Views
876
jondavis1987
J
willow1985
  • Question Question
Not sure if a COUNTIFS formula is the way to go
Replies
5
Views
716
Eric W
Eric W

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top