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

Question:

# Question:Q: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

kc

Posted on

Question marked as Solved

Hi KC,

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

Posted on

Question marked as Solved

Hi KC,

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

Mar 16, 2018 2:49 PM

Thanks Barry - I tried to use the Sumif rule, but without success. Thanks of the help (again!)

~kc

Mar 21, 2018 5:35 AM

User profile for user: KPC

Question: Count Phrase in one Column, and Sum another