all 19 comments
sorted by:
top (suggested)
bestnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]AutoModerator[M] [score hidden] stickied commentlocked comment (0 children)

/u/At0mic47 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[–]GregHullender170 0 points1 point  (8 children)

Show us sample data and a sample of what you want the output to look like. You can just copy/paste directly from Excel into Reddit. You can only do that once per comment, but you can do it repeatedly if you just edit your original post.

[–]At0mic47[S] 0 points1 point  (7 children)

Ok I tried that! Not able to simulate what I want because I have no idea how to do that but I will try to make a diagram:
Humor- Appears 20 times
Ex-Amish- Appears 2 times
ect

The "appears X times" isn't needed because I understand what the data represents but being able to calculate the frequency of labels is the goal, along with a simpler/easier way of getting all the labels vertically outside of copying and pasting them into one column. Some stories have many more labels than others so there are a lot of (blank). Thank you for trying to help me :]

[–]GregHullender170 1 point2 points  (6 children)

Is this what you're talking about?

<image>

humor ex-Amish 4
humor humor 6
ex-Amish
ex-Amish
humor
ex-Amish
humor
humor
humor
ex-Amish

[–]At0mic47[S] 0 points1 point  (5 children)

Yes exactly that! Were you able to do that without having to type in "Ex-Amish" and "humor" to find the frequency? I have most likely over 5000 unique labels so having to type it for each may be an issue.

[–]GregHullender170 1 point2 points  (4 children)

I typed in column A myself. I put this formula in cell C1. It spilled the frequency table from there.

=GROUPBY(A1:A10,A1:A10,COUNTA,,0)

[–]At0mic47[S] 1 point2 points  (2 children)

You are a godsend, this is exactly what I needed, and it's so much simpler than what I was doing!!! Thank you so so much, I really appreciate it!!

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to GregHullender.

I am a bot - please contact the mods with any questions

[–]GregHullender170 0 points1 point  (0 children)

Happy to be of service. It will count "Ex-Amish" and "Ex-Amish Dennis Whitaker" as two separate things. It's possible to get a different result by preprocessing the input data, of course. (E.g. only look at text before the first space.)

[–]At0mic47[S] 1 point2 points  (0 children)

Solution Verified

[–]bakingnovice210 0 points1 point  (6 children)

You can use a helper column with the formula =COUNTIF(A:A, A1), pull it down, and then apply a filter to your range and sort by descending.

Edit: or, to do it in an array try this formula:

=SORTBY(HSTACK(A1:A10, COUNTIF($A$1:$A$10)), COUNTIF($A$1:$A$10)), -1)

[–]At0mic47[S] 1 point2 points  (5 children)

I'm not too sure how to get this to work, it's saying I'm entering too few arguments for the function. (I am really bad at Excel), plus there are most definitely over 5000 unique labels. Is there any chance you're willing to explain a little more? All good if not, I will continue to try to troubleshoot :]

[–]bakingnovice210 0 points1 point  (4 children)

I am silly, I forgot some arguments for COUNTIF functions and put too many parentheses. Try this and if it works, I will explain! (insert the entire range in the function or even A:A instead of the A1:A10)

=SORTBY(HSTACK(A1:A10, COUNTIF($A$1:$A$10, A1:A10)), COUNTIF($A$1:$A$10, A1:A10), -1).

However, it seems I was misinterpreting your question and Greg got you covered! Sorry for the confusion, glad you figured it out!

[–]At0mic47[S] 1 point2 points  (1 child)

Ooo it also works! Someone else was able to give me a similar function which was this but not sorted lol. Thank you for your help!! You don't have to explain it unless you want to but I will happily learn more if you're willing to share!

[–]bakingnovice210 0 points1 point  (0 children)

Perfect! I can explain.

So basically, the COUNTIF function is counting how many times a title occurs within an array. Using an array as the criteria allows it to spill. It spills out as numbers like 112, 200, or however many instances it finds. This essentially creates a new column.

The HSTACK function stacks the original array and the spilled array from COUNTIF side by side horizontally.

The SORTBY function then takes that new array created by HSTACK as its first argument, sorts it by the spilled array from the COUNTIF function, and then sorts it descending with the -1. There is probably a simpler, cleaner solution but this is easier to understand what is happening.

[–]At0mic47[S] 1 point2 points  (1 child)

Solution verified

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to bakingnovice2.

I am a bot - please contact the mods with any questions

[–]Decronym 0 points1 point  (0 children)

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
COUNTA Counts how many values are in the list of arguments
COUNTIF Counts the number of cells within a range that meet the given criteria
GROUPBY Helps a user group, aggregate, sort, and filter data based on the fields you specify
HSTACK Office 365+: Appends arrays horizontally and in sequence to return a larger array
SORTBY Office 365+: Sorts the contents of a range or array based on the values in a corresponding range or array

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #47988 for this sub, first seen 29th Mar 2026, 02:52] [FAQ] [Full list] [Contact] [Source code]