The following table 1
Name the amount
Zhang SAN 300
Li si 200
Zhang SAN 500
Yang, 2 100
Six 20 ma
Yang two 30
Zhang SAN 10
Li si 70
Fifty and sixty
Total 1290
Want to through the table 1, press the name automatically produce a piece of table 2, the contents of the table 2 requires repeated my name column of table 1, the content of the column and the amount of Numbers to merge, it is concluded that no duplicate name and amount, namely:
Name the amount
Yang, 2 130
Zhang SAN 810
Li si 270
Fifty and sixty
Six 20 ma
What functions can be achieved with excuse me?

CodePudding user response:

Direct function must not

Vba to do.

CodePudding user response:

Function requires concluded that don't repeat the names of people first, then using SUMIF function obtained by total

Pivot table is one of the most simple way

CodePudding user response:

Do not need to function, the classified summary, calculation can be combined,

CodePudding user response:

The basic function of the pivot table

CodePudding user response:

Directly inserted into the pivot table

CodePudding user response:

Must function can be achieved, set in table 1. A, B, the first line
Input in the input 1 C1, C2=the IF (COUNTIF ($A $1: A2, A2) & lt;> 1, 0, COUNTIF ($C $1: C1, & gt; "" 0 ") + 1)
C2 began to drop down to the last name, so don't repeat the names of the corresponding serial number 1, 2, 3,... ,
Table 2 apply line number and the serial number of the LOOKUP take name
Summary use SUMIF

CodePudding user response:

Suggest pivot table
If you must function
1, the index + small + if
2, sumif

CodePudding user response:

A similar problem, in reference

CodePudding user response:

Direct function must not, directly inserted into the pivot table

CodePudding user response:

If it is to achieve the result of this need input array formula

I copied the formula directly to come
=INDEX ($B $1: $B $10, SMALL (IF (MATCH ($B $1: $B $10 & amp;" ". $B $1: $B $10 & amp;" ", 0)=ROW ($1, $10), ROW ($1, $10), 9 ^ 9), ROW (B1))) & amp;" "
B1:=SUMIF (B10, E1, C1: C10)
Finally it's important to note that the first formula is array formula, need to hold down Ctrl + shift + enter three hold the end at the same time