Excel help

Discussion in 'General Chat' started by lostinspace, Sep 6, 2012.

  1. lostinspace

    lostinspace
    Well-known Member

    Joined:
    Apr 30, 2004
    Messages:
    1,959
    Products Owned:
    0
    Products Wanted:
    1
    Trophy Points:
    116
    Ratings:
    +738
    I thought I was fairly good with excel but can't work out what I'm doing wrong. I think I'm missing something obvious.

    It's based around 2 columns in my spreadsheet.

    Column 1 lists the month a client contacted me- every January contact has "Jan" in the cell, February contacts have "Feb" in the cell etc.
    I can count how many contacts I have in each month.

    Column 2 may contain anything (date usually) or be blank.

    I need to count how many cells in column 2 contain an entry (of any kind) for each month.

    I can count how many cells have a value for the whole of column 2, but not when linked to what's in column 1.

    Different months have different numbers of contacts.

    Eternal thanks to anyone who can give me back my sanity.:lease:
     
  2. imightbewrong

    imightbewrong
    Distinguished Member

    Joined:
    Dec 6, 2005
    Messages:
    53,517
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    167
    Location:
    Romford-ish
    Ratings:
    +34,173
    could use COUNTIFS for this. First column as criteria column 1, criteria 1 as "Jan", second column as criteria column 2, criteria 2 as "<>" (for 'not blank')

    Actually criteria 1 should be a cell with Jan in, then have a 12-row column Jan, Feb etc and drag the forumla down.
     
  3. mikenw

    mikenw
    Active Member

    Joined:
    Aug 29, 2006
    Messages:
    319
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    28
    Ratings:
    +28
    Either set up cells with jan, feb on another tab etc , and use countif on the whole range of your records.

    Or pivot it, with a count on date
     
  4. lostinspace

    lostinspace
    Well-known Member

    Joined:
    Apr 30, 2004
    Messages:
    1,959
    Products Owned:
    0
    Products Wanted:
    1
    Trophy Points:
    116
    Ratings:
    +738
    Thanks mate- I feel a right nob now!
    I was using "*" not "<>".

    I need to lay down now.

    I'll buy you a pint next time I see you.
     
  5. imightbewrong

    imightbewrong
    Distinguished Member

    Joined:
    Dec 6, 2005
    Messages:
    53,517
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    167
    Location:
    Romford-ish
    Ratings:
    +34,173
    Any time - mine's a Poacher's Choice if they have it :)
     

Share This Page

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice