+ Reply to Thread
Results 1 to 14 of 14

basic countif function

  1. #1
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    324

    basic countif function

    hey guys

    having a brain malfunction today

    i need a simple formula, where it looks at the name col, and counts how many time, each status showed up under that name

    for eg mason had 3 live status
    fred had 3 qualfied
    danielle 3 sent
    3 risk
    3live

    i no this is basic question so i appreciate your helpweeknum.xlsx

  2. #2
    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: basic countif function

    Hi

    In D4 and copy down.

    =SUMPRODUCT(($B$4:$B$18=B4)*($C$4:$F$4=C4))

    Is this works,for you?
    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.

  3. #3
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    324

    Re: basic countif function

    thank you for your response. greece is a lovely country, i have this friend in patra

    for some reason i cant get tht formula to work

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: basic countif function

    How about a Pivot table.

    Drag the Name and Status to Row Labels, then the Status dragged again to Summation area...
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    324

    Re: basic countif function

    hey nbvc i have over 60,000 records, and already built a summary sheet, so i need to have a formula

  6. #6
    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: basic countif function

    .."Sound" strange to me..

    Pls, take a look to the example.


    So you have a Greek(?) friend in Patra?

    Have you ever visit Greece?
    Attached Files Attached Files

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: basic countif function

    Then you would need COUNTIFS

    e.g.

    =COUNTIFS(Sheet2!B:B,A2,Sheet2!C:C,B2)

    where A2 and B2 contain a name and status respectively... copy formula down.

  8. #8
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    324

    Re: basic countif function

    hmm , okay. i play around with it

  9. #9
    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: basic countif function

    Ok....I really don't know.. Apologize..

    This is the corect formula.

    =SUMPRODUCT(($B$4:$B$18=B4)*($C$4:$C$18=C4))

    Copy down.
    That's why formula does not works for you...

  10. #10
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    324

    Re: basic countif function

    guys unless i am being super thick, but i am pretty sure the formula should be

    =COUNTIFS(B4:B18,B4,C4:C18,C4)
    b4:b18 is looking at the range, b4 is selecting who i want, then c4:c18 is looking at the status, and c4 is the specfic status. and that gives me the total. thats the correct formulas i belive

  11. #11
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    324

    Re: basic countif function

    Fotis check private msgs

  12. #12
    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: basic countif function

    Yes. Corect. I am sure that NBVC didn't noticed that your data starts in Row 4...

    As you are in Excel2007, this is better solution than SUMPRODUCT(The second one) that also works.

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: basic countif function

    Quote Originally Posted by masond3 View Post
    guys unless i am being super thick, but i am pretty sure the formula should be

    =COUNTIFS(B4:B18,B4,C4:C18,C4)
    b4:b18 is looking at the range, b4 is selecting who i want, then c4:c18 is looking at the status, and c4 is the specfic status. and that gives me the total. thats the correct formulas i belive
    That is similar to what I have suggested.. except I assumed your summary was in a separate sheet....

    If not, then

    =COUNTIFS($B$4:$B$18,B4,$C$4:$C$18,C4)

    copied down...

  14. #14
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    324

    Re: basic countif function

    NBVC i no , i was having a blonde moment, thanks for your help guys

+ 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