+ Reply to Thread
Results 1 to 8 of 8

How do I use countif or countifs, and how?

  1. #1
    Registered User
    Join Date
    05-23-2012
    Location
    Alexandria, Virginia
    MS-Off Ver
    Excel 2010
    Posts
    17

    How do I use countif or countifs, and how?

    Update: Do I use countif or countifs? I have a sheet with numerous tickets (rows) in it, and each row has a part number in column E some are the same most are different. On a seperate tab I need to populate a table with only one of each part and then give a count as to how many rows have that part number in column E. But i only want a count if there is an end date (of any value/date) in column H.

    Original Post: I need a formula that will count all rows that have a specific value in them and also a end date. The end date specifies if the ticket is closed. So basically I am looking for a formula that will count the number of tickets with (generic part number) in column 4 of all tickets and that has a end date (of any value) in column 8.

    Please help
    Last edited by xodiak; 06-01-2012 at 10:30 AM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Count if?

    Have you looked at Countifs?
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    05-23-2012
    Location
    Alexandria, Virginia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Count if?

    I have I came up with this. =SUMIFS(Sheet1!E:E,Sheet1!H:H,???) Sheet1E:E is the part number column I'm not sure how to write it to look for a certain value, Then sheet1H:H is the date column which how do I write it so if there is a value (date) it counts it?

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count if?

    Hi

    In your title, you say Countif

    In your previous post you say for SUMIFS.. Do you want to count or to add?

    Can you upload a small sample workbook?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    05-23-2012
    Location
    Alexandria, Virginia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Count if?

    Sorry copied the wrong cell, its way to early in the morning. =COUNTIF(Sheet1!E:E,Sheet1!H:H,???) Thats the basic formula I'm using or attempting to use. The only version I was able to get working was a slight variation =COUNTIF(Sheet1!E:E,A2) but obviously then it doesn't look to see if there is a date in the H column

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How do I use countif or countifs, and how?

    First thing to do, create a unique list. You can do this with Advanced Filter

    After you create the unique list from column E, you can now use =COUNTIFS(E:E,K2,H:H,">0")

    In this case I extracted the unique list to column K and then in L2 I used the formula above.

    I used greater than 0 because in Excel a date is recognized as a number.

  7. #7
    Registered User
    Join Date
    05-23-2012
    Location
    Alexandria, Virginia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: How do I use countif or countifs, and how?

    Perfect thank you that worked exactly as i wanted it to. The greater then 0 is basically just recognizing cells with data in it correct?

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How do I use countif or countifs, and how?

    Well, let's be specific, cells with numbers, not just data.

    Glad you have something you can work with and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1