Sum the number of drugs with the same name, the same specification and the same purchase price, and

1. The following figure shows the data that needs to be filtered with the same name and specification, and then the total quantity and amount are calculated**.

2. Select the ** and click Insert "Pivot Table" in the toolbar.

3. Then on the right side of the page, drag the name and specification into "Filter", and drag the quantity and amount into "Value".

4. Then click the drop-down box of the filter in the name column and select "Refrigerator".

5. Then select the model "BCD-01" in the drop-down box of the specification column and click OK.

6. You can count the sum of the quantity and amount of "BCD-01 refrigerator" that needs to be selected.

Stats product name = "tennis shoes."", product specification="40" sales quantity. L2 formula: =sumProduct(($d$2:

d$14=$j2)*(e$2:$e$14=$k2)*(f$2:f$14))

Stats product name = "tennis shoes."", product specification="40" sales amount. m2 formula: =SumProduct(($d$2:

d$14=$j2)*(e$2:$e$14=$k2)*(h$2:h$14))

Breakdowns.

Menu - Data - Sorting, sort by the name of the first key word, and the specification of the second key word.

Menu - Data - Breakdown Summary.

If you have these data in A1:D10, where the first line puts the name, ask to calculate the total amount of the same name and the same specification in column e, and enter it in e2.

Pull down the formula of e2 to e10 with the autofill handle, and you can adjust the cell address by itself.

See this example action animation:

Table 1 If the name is in column A and the specification is in column B, insert column C and enter formula =A2&B2 for C2

Table 2 Insert column C, enter formula =A2&B2 for C1 and enter simif for column D and D2 (Table 1!).c:c,c1, Table 1!d:d)

It's best to use pivot tables or categorical summaries, if you don't want to use these two, I don't think of how to implement the function, but I can help you write a vba implementation. If you need to send the sample table to my mailbox, it is recommended that you use a pivot table or classification summary, which feels a bit complicated with VBA.

Enter in g2.

sumproduct(($b$2:$b$1001=b2)*(c$2:$c$1001=c2)*(f$2:$f$1001=f2)*$e$2:$e$1001)

Pull-down padding.

The dumbest method is functions, followed by categorical summarization.

Priority is given to PivotTable processing of this type**.

In your case, just use the classification.

This can be achieved using "Consolidated Calculations" under the "Data Tab" in Excel. The specific steps are as follows:

1. Locate the mouse to the first cell where you want to display the result of "Project Summary Summation". For example, the picture on the left shows the results of each subject of the examination of students A, B, and C, and now the total scores of students A, B, and C are required to be displayed in the cell starting from E3, and the mouse is positioned to E3.

2. Then, select "Merge Calculation" in the "Data Tab", pop up the "Merge Calculation" window, select "Summation" at the function (the default is "Summation", generally do not need to manually select), locate the mouse to the reference position, select the data area to be counted (note: the items to be counted must be in the first column), then click "Add", check the "first row", "leftmost column", and click "OK".

In the end, we get the result on the right, and we see that it is not what we wanted. We only need the overall grades of the three students, A, B, and C, we don't need the grades of the other students, and we don't need the course name column.

3. If we only want the total score of students A, B, and C, we should first enter students A, B, and C in the first column of the display results, and enter "score" in the second column.

4. Select student A, B, C and the position where the grades are displayed as the area for the result display, then click "Combine Calculation" under the "Data Tab", select the statistical area by referring to the location, check the leftmost column and the first row, and confirm.

5. In this way, we get a summary of students' A, B, and C grades, and the values shown in F4 to F6 are the total scores.

This can be achieved with sumif. Illustrate.

Excuse me: I made a table of product sales statistics, and now I encounter a situation. That is to say, the products we sell have several different specifications and models.

Now it's all in one **! How can I select the sales quantity of the same model of the product in ** for automatic summing? The data is not very much, there are about 4-5 different specifications and models!

Now the purpose is to classify the sales of these different specifications and models! A friend who knows will tell you, thank you!

1. Open Excel** and enter the data you need to calculate in the cell.

2. Select the "Combined Calculation" option in the "Data" column in the toolbar.

3. In the pop-up dialog box, select the cells with data in column A and column B in the reference position.

4. Click Add again, check the front of the "leftmost column", and click OK.

5. You can automatically generate the sum of data with the same name in **.

=sumif(a:a,"Zhang San",b:b)

This formula is to find the sum of column A is Zhang San, corresponding to the value of column b.

Combined calculations can be used to automatically sum data under the same name.

Next"Right - "Next" - Layout", press and hold "Name" and drag to "Row" to let go, drag "Data 1", "Data 2", "Data 3", "Data 4" to "Data" - OK - Complete", as shown in the figure: Right-click in "Count Item: Data 1" (B4 "cell) - "Field Settings" - Sum "- OK", use the same method to change "Count Item:

Data 2", "Count Item: Data 3", "Count Item: Data 4" are changed to sum.

As shown in the figure: in the "blank" ("A12" cell right-click - "hidden", as shown in the figure: you can place the cursor in the "B3" cell, and drag it back when aiming at the lower line into an arrow cross (movable flag), the effect is as shown in the figure

Each time you add or modify data, simply right-click in the PivotTable - "Refresh Data" (or direct point). If you only want to check the data of a certain person, you can drag the "Name" of the "A4" cell to the "Page Field" of the "A1" cell, as shown in the figure: click the triangular tip of the "B1" cell, and click the name of the person you want to check.

=sumif(a1:a16,"Zhang San", b1:b16) or as.

In the figure, the capacity in the grid = sumif(a1:a16,a17,b1:b16)--

How to quickly sum data with the same name? It's very easy to learn this method.

It can be achieved with pivot tables, or categorical summarization. The details are as follows: Click Insert -- pivot table, follow the prompts, drag and drop the data to come out, this realization is the sum of the data under the same person's name, or it can not be summed, it is counting, average, etc., the method is similar.

You can start by filtering by name.

The sum is then calculated.

Anything that needs to be counted.

Glance.

Insert: Pivot table, drag the required fields into the table.

How excel can quickly sum and summarize multiple worksheets.

Function formula = sum(sheet1:sheet4!b3)。

Take Excel2011 as an example:

1. Open Excel**, and sum the sheets1 to sheet4 into the total table.

3. Select cell B3 and enter the function formula =sum(sheet1:sheet4!b3), press enter to confirm.

5. Finally, enter the formula in the same way, press and hold CTRL+Enter, and the sum of the data from Sheet1 to Sheet4 will be completed, so that the operation can be completed.

1. In the same excel file, open the file table.

2. The red font of data sheet2 is represented, which is located in two worksheets that you don't understand.

3. The data in data table 1 is displayed in blue font of data sheet1.

4. If you want to ask for data from two worksheets, first select a blank cell, and then manually belong to the equals sign "=" in the cell; Select the blank cell in sheet1 again to belong to the equals sign.

5. Then select the sum of the two data in the two worksheets, that is, the sheet, click sheet1 first, and then belong to the plus sign "+" in the displayed data

6. Then go to sheet2 to select a data, pay attention to whether the above display is different and there is a "=b2+shee", which indicates the sum between two different worksheets.

7. Go back to sheet1 to see that the data in two different sheets have been summed.

8. Then double-click and click on the cell to find one, and the summing formula will be displayed.

Suppose your three worksheet names are m, n, g (arbitrary, no need to be regular); If the conditions (tentative name d) are in the range A1 to A5, and the data to be summed are in B1 to B5 (also arbitrary), then the formula is:

sum(if(m!a1:a5="d",b1:

b5),if(n!a1:a5="d",b1:

b5),if(g!a1:a5="d",b1:

b5)), and then press shift+ctrrl+enter.

??I don't understand, you're using arrays, right? It has nothing to do with the name of the worksheet, the statistics are almost the same.

This one is also statistical, the same as the one you use.

1. Select the data region on the left.

2. Insert a pivot table.

3. Drag "Name" into the column field, "Color" into the row field, and "Quantity" into the data area 4 to generate the right effect.

Because of the large amount of declaration information, in addition to the formula drop-down of [squinting eyes string needle] above the G2 input, you can also use the formula drop-down in F2 to avoid the trouble of text input: =if(g2="","",concatenate(a2,"(",b2,")"))

Use the sumif function on it and follow your diagram.

In g1, fill in =sumif(b:b,"Red", c:c), which means to find the sum of the number of columns c corresponding to "red" in column b.

d2 Enter the formula.

if(sumproduct((a$2:a2=a2)*(b$2:b2=b2))=1,sumproduct((a$2:

a$1000=a2)*(b$2:b$1000=b2)*c$2:c$1000),"")

Just drop down.