Announcement

Collapse
No announcement yet.

Tally Count Of Values In Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

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

    Try the COUNTIF function...

    =COUNTIF(Range,Criteria)

    Hope this helps!

    Comment


    • #3
      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

      Comment


      • #4
        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, 04:53. Reason: Auto Merged Doublepost

        Comment


        • #5
          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!

          Comment


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

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

            Comment


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

              Comment

              Working...
              X