countif, unknown numbers to count

Jennifre

Board Regular
Joined
Jan 11, 2011
Messages
147
Thanks to everyone who has been so helpful to my incredibly dorky and inexperienced questions!

I have yet another one:

I'm hoping to countif down one column, and what I'd ideally like returned is both what is being counted, as well as how many of each it counts.
For example:

D2:D1364 has many values, so I'd like a count of which values are there + how many of each, ideally next to each other, like this --

column E (#s):
1111
1112
1121

column F (counts):
301
25
103

Also, if / when I sort this, do I need to exclude these totals from the sort; i.e., will sorting all of the data mess up this count?

Thank you all, again!! :)
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

mancemonster

Board Regular
Joined
Feb 24, 2011
Messages
89
Something like...

=COUNTIF(D:D,D1)&"-"&D1

??

Not sure what the exact question is... Can you post some sample data?

Regards

Roger
 

Jennifre

Board Regular
Joined
Jan 11, 2011
Messages
147
Thanks Roger... I don't know how to post data, though I'm not sure how to make my question more clear. Um...

I need a count of the values in one column. Ideally, I'd like to see the values that populate the column in one column, with their totals in the next column so they are next to each other.

Is that more clear?

IGNORE THIS (below) IF MY CLARIFICATION ABOVE CLARIFIES MY QUESTION.
I tried your formula and saw how that can give me a count of a certain value in one cell, down that column, though I need the results to list the values in that column as well as give me a count of them. (I tried expanding the cell to the cell range, though it still returned only the starter cell's value + count.)

Thanks....
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,958
Office Version
  1. 365
Platform
  1. Windows
So if I get that right, look at column D, make an ascending list of unique entries in column E, then count them in column F.

I'm sure it can be done with formula, but think it would be easier with VBA if you can use it.

Please test on a COPY of your workbook to prevent data loss.

Code:
Sub test()
With ActiveSheet
    .Range("D2", Range("D2").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "E2"), Unique:=True
    .Names("Extract").Delete
    With .Sort
        .SortFields.Add Key:=Range("E2"), SortOn:=xlSortOnValues, Order:=xlAscending
        .SetRange Range("E2", Range("E2").End(xlDown))
        .Header = xlNo
        .Apply
    End With
    .Range("E2", Range("E2").End(xlDown)).Offset(, 1).FormulaR1C1 = "=COUNTIF(C4:C4,RC[-1])"
End With
End Sub
 
Last edited:

Jennifre

Board Regular
Joined
Jan 11, 2011
Messages
147
Jason, thank you. Yes, that's exactly what I'm hoping for.

I will try to investigate what your suggestion is and how to use it... thanks :)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,958
Office Version
  1. 365
Platform
  1. Windows
In excel, with the sheet holding the data selected.

Alt + F11 to open the VBA editor.

Select insert, then module in the menus.

Copy the code in my reply, then paste into the VBA editor.

Press F5.

NB: E2 needs to be empty before you press F5 or the code will fail.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,297
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top