Numbers Sum of all values in a column where another cell within the row matches text

numbers

I'm sorry if the title is confusing. I'm struggle bussing hard on figuring out how to write some formula in Numbers. It really doesn't help that the name of the program and functions I'm trying to use are so generic, I get random completely unrelated math results with every search!

I have a table like this (obviously a lot more data):

ID    number
01    24
01    12
03    62
05    12
01    1

I have another table like this:

ID    count    total
01    3
03    1
05    1

I'm trying to write a formula to get the sum of each Id's numbers. I managed to get the count using COUNTIF(table1::B:B,$A:2), and copy / pasting that down the count column. Sweet! That was a pain, but I got it. Now I want to get the total of all their numbers. I know it's going to look something like this: SUM( <iterate over range, if table1::A matches A, add table1::B>) But I'm struggling to figure out both how to do this and how to word searches to get relevant results.

So, in my second table, 01's total should be 24 + 12 + 1 = 37.

I would love both some help in figuring this out (I'm totally OK with being pointed to relevant functions and can figure it out from there!), as well as some tips on generally how to search for stuff like this. It stresses me out endlessly that they named the program Numbers and made it near impossible to find what I need.

Thanks!

Best Answer

I've found it!

I opened the function browser and read the description of every function down the list until I finally found the SUMIF function! SUMIF(table1::ID, $A2, table1::numbers)

The first parameter creates a range across the whole column with header ID in table1, the second is a strict column reference with a relative row reference, so I could copy / paste down the whole column, and the third parameter is the entire column with header numbers in table1.

Related Question