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

Question:

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

Reply
Question marked as Solved
Answer:
Answer:

Hi KC,


SUMIF is your friend here.

User uploaded file

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

Page content loaded

Question marked as Solved

Mar 16, 2018 2:49 PM in response to KPC In response to KPC

Hi KC,


SUMIF is your friend here.

User uploaded file

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

Reply Helpful
User profile for user: KPC

Question: Count Phrase in one Column, and Sum another