COUNTIF · pivot table · QUERY
Count Instagram comments per person
Who commented the most, who commented once, and who only replied to others. Export the comments, and one formula or one pivot table answers all three.
Recent exports
| post | network | comments | date | status |
|---|
Only exports made in this browser are listed.
The one-formula answer
In the exported sheet, username is column C. Next to the data, in the first empty column, row 2:
=COUNTIF(C:C, C2)
Fill it down. Every row now shows how many comments its author left on the post. Sort by that column, largest first, and the most active commenters are at the top (each appears once per comment, so filter to unique usernames or use the pivot below for a clean list).
A clean list: pivot table (Excel or Sheets)
- Select the data. Excel: Insert, PivotTable. Sheets: Insert, Pivot table.
- Rows:
username. - Values:
comment_id, summarised by Count (Excel) or COUNTA (Sheets). - Sort the count descending.
Add is_reply to Columns and each person's total splits into top-level comments and replies. Someone with ten replies and no comment of their own is a conversation partner, not an entrant.
Google Sheets in one cell
=QUERY(A1:L, "select C, count(B) where C is not null group by C order by count(B) desc label count(B) 'comments'", 1)
Want only top-level comments? Add and H = 'no' after is not null. Want people with three or more? Wrap it: =FILTER(QUERY(...), INDEX(QUERY(...),,2)>=3), or simply sort and read down.
Excel 365: a live table with UNIQUE and COUNTIF
On a new sheet, in A2: =UNIQUE(Comments!C2:C5000). In B2: =COUNTIF(Comments!C:C, A2#). Both spill down automatically. Sort with =SORTBY(A2#, B2#, -1) if you want the list ordered without touching the source.
Make sure the counts are real
A per-person count is only as good as the list underneath it. If an export stopped after a fixed number of seconds, the people who commented later look like they never commented at all, and the people who commented early look more active than they were. Here a free export reads every comment and reply on posts with up to 1,000 comments, and the page shows comments read next to Instagram's count before you download. Check it in the sheet with =COUNTA(B:B)-1.
Go further with the counts
- Split comments from replies. Someone with twelve rows may have written one comment and eleven replies. The Commenters sheet has both columns; how replies are stored is on the page about exporting comments with replies.
- Slice by another field. A pivot table with
usernamein Rows andis_replyin Columns does it in four clicks: pivot tables for comment exports. - Count across several posts. One post shows who was loud once. Five posts show who keeps coming back: how to find your regular commenters.
- Set the copy-paste accounts aside. An account with forty identical comments is not your most engaged follower: detect bot and spam comments.
- Use the counts for a giveaway. If each comment is an entry, the totals are the weights: how to pick a giveaway winner in a spreadsheet.
- Do the same on other networks. The Commenters sheet has the same columns for a TikTok video, a YouTube video or a Facebook Page post.
Questions
Should replies count?
Depends on the question. For engagement, yes. For a giveaway that says one comment per entry, usually only top-level comments count: filter is_reply to no first.
Is username or user_id better for counting?
Within one export they give the same answer. Across exports made weeks apart, user_id is safer because usernames can change.
Can the page show this without a spreadsheet?
Yes, the export page lists the most active commenters. The spreadsheet is for when you need the full table or want to combine it with other data.