Sum counts in a column based on string contents in another column

[cbharder]

Hi forum,

I need to sum the counts of a very long column based on an associated string in another column. Like this:

the1first,category 3
the2second,category 5
this2first,category 124
the1third,category 13
that1first,category 9
the2second,category 10
the3third,category 7

What I need is something like this

first 136
second 15
third 20

I could, of course, sort these columns, but this is a bad option with many rows. Any smart suggestions would be greatly appreciated.

Best, Christoffer

[Pete_UK]

You could use SUMIFS with a wildcard character. If you have first, second, third etc. listed down column F with your date in columns A to C, then you could use this in G1:

=SUMIFS(C:C,A:A,"*"&F1&"*")

then copy down.

Hope this helps.

Pete

[cbharder]

Thanks for your reply, but I unfortunately cannot really make sense of it and it does not work (likely my own fault/incompetence in explaining). If I have

the1first,category

in F1, then your formula gives me NAME?

I have several columns with numbers, and I need a separate count for first, second and third etc. for each. Here is a screenshot of a fraction of my actual table - perhaps that is better. Attachment 638895

I would like a total count of each category in A-C 20-22. Hope this was clearer. Best, Christoffer

the1firstcategory 3
the2secondcategory 5
this2firstcategory 124
the1thirdcategory 13
that1firstcategory 9
the2secondcategory 10
the3thirdcategory 7

[Pete_UK]

I cannot see your attachment - it comes up with Invalid Attachment. A picture is not much use to us anyway - it is better to attach a sample Excel workbook, and then we can try different things out.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Your profile states that you are using Office version 7/8, but I don't know what that means. If it is Excel 2003 or earlier, then SUMIFS did not exist in those versions - you will need to use SUMIF instead, though the syntax is a bit different:

=SUMIF(A:A,"*"&F1&"*",C:C)

This assumes that the numbers you want to add are in column C.

I'm not sure where Alsike is, but you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete