Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Tally Count Of Values In Column

  1. #1
    Join Date
    15th December 2007
    Posts
    3

    Tally Count Of Values In Column

    I have a list of varying IP addresses in a column. I need to create a formula that will tell me how many times an IP address appears in the column so that I can ultimately determine the most common IP address listed.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,287

    Re: Count The Number Of Times A Value Appears In A Column

    Try the COUNTIF function...

    =COUNTIF(Range,Criteria)

    Hope this helps!

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Count The Number Of Times A Value Appears In A Column

    serasbach,

    Welcome to Oz.

    Did you not find an answer among the serveral "Possible Answers" at the top of your post? There are several that apply to your situation as this question has been asked and answered before.

    One answer from one of the "POssible Answers": =COUNTIF(A1:A100, "text")

    Take some time to review search results first before posting a question.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  4. #4
    Join Date
    15th December 2007
    Posts
    3

    Re: Count The Number Of Times A Value Appears In A Column

    AAE, thank you for your respose. I actually did NOT find the answer as you mentioned, as the countif didn't work exactly as I needed it to. And since I was being a good boy and not ASSUMING that countif was what I needed (although trying it extensively) I was following Dave Hawley's guidelines when seeking assistance.

    Because the "text" portion of the formula varies, it doesn't help me to have "text" as the input criteria. And when I try this countif(A1:A100, B2) I get a column of values that descend rather than give definitive values. This is because when I paste the formula through the column the first value increases as it goes down the cell.

    Let me give you an example of what I mean....

    When I use countif(A1:A100, B2), I get results as follows:

    10.129.1.1 4
    10.129.1.1 3
    10.129.1.1 2
    10.129.1.1 1
    10.129.1.2 14
    10.129.1.2 13
    etc...


    What I need are results like:

    10.129.1.1 4
    10.129.1.1 4
    10.129.1.1 4
    10.129.1.1 4
    10.129.1.2 14
    10.129.1.2 14
    etc...

    Does that make sense? The descending values are due to the dynamic nature of countif(A1:A100, B2), The A1 portion increases automatically- if there is a way to make this value static- that is probably one way to solve this.

    Thanks.
    Auto Merged Post;

    Let me add a bit more to this. If I use countif(A1:A100, B2), how can I make the A1 value static, and the B2 value dynamic in this function? This would work as a possible solution.
    Last edited by serasbach; December 15th, 2007 at 03:53. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,287

    Re: Count The Number Of Times A Value Appears In A Column

    Make the reference to A2:A100 absolute...

    =COUNTIF($A$2:$A$100,B2)

    Hope this helps!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    15th December 2007
    Posts
    3

    Re: Count The Number Of Times A Value Appears In A Column

    That's Exactly It!! Thanks so much!!

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

    Re: Count The Number Of Times A Value Appears In A Column

    And since I was being a good boy and not ASSUMING that countif was what I needed (although trying it extensively) I was following Dave Hawley's guidelines when seeking assistance.
    Thank you!

    BTW, a one Column PivotTable would give you a tally count

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Keep Tally Of Team Scores
    By matrix281 in forum EXCEL HELP
    Replies: 3
    Last Post: January 19th, 2008, 22:49
  2. Tally Report Of x
    By sriya in forum EXCEL HELP
    Replies: 4
    Last Post: August 28th, 2007, 15:41
  3. Count Unique Values, For Specific Values
    By CallMeJD in forum EXCEL HELP
    Replies: 4
    Last Post: September 14th, 2006, 10:07
  4. Tally Total
    By Danian in forum EXCEL HELP
    Replies: 8
    Last Post: August 5th, 2005, 02:54
  5. Tally rows
    By ceepee2 in forum EXCEL HELP
    Replies: 7
    Last Post: March 30th, 2005, 19:29

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
  •  
porno