Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Level 1

7 points

Count Phrase in one Column, and Sum another

Hello -

Hoping you can help with this. I have this kind of data:

Title	Royalty
Song 1	.56
Song 1	2.36
Song 3	1.25
Song 1 Test	2.58
song 2	.25
Song 5	.78
Song 1	5.23

and so on. What I want to do is sum up the royalties for each song, (an added bonus would be to use a wild card so that in my above example, it would count Song 1 and Song 1 Test as the same song). I am doing this in a separate table, but I don't think that matters that much, as I know how to reference other cell in other tables.

I tried finding a formula like this from excel: =INDEX($A$1:$A$16,MODE(MATCH($A$1:$A$16,$A$1:$A$16,0))) but it doesnt work and searching around here I have found that Numbers doesn't do this formula like Excel Does. So, I know there is an answer, but I can't find it.

Thanks - as always- for the help.

Cheers

Posted on Mar 16, 2018 1:30 PM

Best reply

Barry

Level 9

59,861 points

Posted on Mar 16, 2018 2:49 PM

Hi KC,

SUMIF is your friend here.

I tested initially with COUNTIF, as it was easier to immediately see if the condition (which includes a wild card) included the "Test" case (which it does).

I was surprised to see that Numbers correctly interpreted the numbers, despite some using "." as the decimal separator, and others using ",".

The two formulas used are:

B2: COUNTIF('Table 1-1'::A,A2&"*")

C2: SUMIF('Table 1-1'::A,A2&"*",'Table 1-1'::B)

Both filled down their respective columns.

Table 1-1 is the bottom table, containing the data.

Regards,

Barry

View in context

2 replies

There are no replies.

Count Phrase in one Column, and Sum another