Numbers add sum based on text/names

Question

Level 1

0 points

Numbers add sum based on text/names

Hello,

I need a formal to add the sum of names. For example, I have in the first table names as Menten, he paid 300$ and again 300$, and Dieter paid 400$. Together I got 1000$. Which is correct, but now I wanna know later on, how much Menten just paid to me, not including Dieter and all other names.

In the table below, I have Menten and has a sum of 600$. Which is correct, but how can I put that as a formel. So, if I put in the first table Menten in again with $200, it should automatically add $200 to the $600 in table 2. Do I need to use Sumif and put a range in there?

Actually, what would be much better, whatever name I put in row A in table 2, that should put the sum in row 2. So I can add more customers to it.

Posted on Feb 14, 2014 6:40 PM

Reply

Answer 1

xynlovesit Author

Level 1

0 points

Feb 14, 2014 7:13 PM in response to xynlovesit

Hmm,

I chose now the row C in the first table just for testing, but it doesn't work. All I get is 0

The formular I use is

=SUMIF(A2:A4,"Menten",B2:B4)

Got it: Supposed to be:

=SUMIF(A2:A4,{"Menten"},B2:B4)

Reply

Answer 2

Barry

Level 9

60,035 points

Feb 15, 2014 2:18 AM in response to xynlovesit

A better formula would be the one shown here:

=IF(LEN(A)<1,"",SUMIF(Data :: $A,A,Data :: B))

The core formula, in bold differs form the one you wrote in these ways:

It is placed on a table separate from the one used to record the date. Data:: tells Numbers that $A and B are references to cells or ranges of cells on the table named Data.
Numbers 'knows' the first and third arguments of SUMIF are single column ranges of cells, and the second argument is a single cell, so it is able to interpret these single letter references correctly.
$A references all of column A (on Data) except for Header rows and Footer rows (if these are defined).
A references the cell in the same row of column A on the same table as the formula.
B references all of column B (on Data), again excepting Header and Footer rows, if these are defined.
The condition value ("Menten" in Row 2) is picked up from the cell in column A of that row, and from the cell in column A of succeeding rows as the formula is filled down.
The enclosing IF statement is used to keep summary cells 'blank' in rows where no name has been placed in column A of that table.
LEN(A) returns the length, in characters, of what's displayed in the column A cell on that row. If that lencth is less than 1 (ie. nothing has been entered in the cell), IF returns a null string, which makes its cell appear empty, otherwise (when an entry has been made in column A of that row), IF activates the calculation of the core formula.

Regards,

Barry

Reply