Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Tally Count Of Values In Column

1. I agreed to these rules
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. Super Moderator
Join Date
4th July 2004
Location
Posts
2,371

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

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

4. I agreed to these rules
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. Super Moderator
Join Date
4th July 2004
Location
Posts
2,371

## 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. I agreed to these rules
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. ## 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

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

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