Excel data statistics problems

<> input =if(b4<5,b4,in c14"0"), c23 input = if(b4>5,b4,"0")

You don't have a batch on your first chart, I don't know how you added it, or if it's all 1 by default.

Use pivot tables.

Select any data cell in the first table, click the menu "Data" - pivot table and pivot chart "- Next" - the data area will be automatically identified here, click "Next" - Layout - drag "Address" to the left "row", drag "Address" and "Quantity" into the "Data" box, OK.

If you add or modify the content of Table 1 in the future, right-click in the pivot table to refresh it.

Suppose your source data is an ABCD column, a number less than 5 is an FGHI column, and a quantity greater than or equal to 5 is a KLMN column.

The number of statistics is less than 5.

Batch number = countifs(b:b,if(b2<5,b2,0),c:c,f2).

Total =sumifs(b:b,c:c,f2,b:b,if(b2<5,b2,0)).

The number of statistics is greater than or equal to 5.

Batch number =countifs(b:b,if(b2>=5,b2,0),c:c,k2).

Total =sumifs(b:b,c:c,k2,b:b,if(b2>=5,b2,0)).

I don't know what you mean, is the landlord going to calculate the total?

Batches can be counted if the total can be sumif, q2535926560 you add me.

Method 1:First, turn all the names into a single column, as follows:

Enter =b3 in cell A34

Then fill to the right to the edge of the name, i.e., the T34 cell.

At this point, the data of several columns is summarized into a single column.

Then copy all the names of the single column to the Y column.

<> Finally, enter the formula: =countif($a$3:$t$33,y2) in column W2

Then fill it down.

Method 2:

<> select the data region, next.

Both the existing worksheet (you have to select a location) and the new worksheet are OK, click Finish.

At this time, the pivot table is generated, right-click on the value field in the pivot table field on the right, and select "Add to Row Label", and you're done.

It is recommended to use **, manual operation can also be used, but there are many steps.

Copy column A to column B and select Remove Duplicates.

c2=countif(a:a,b2)

Use the following for a quick process.

How Excel can quickly extract the number of unique data.

If you don't understand the last half of your sentence, the following column shows the data from the previous statistics.

It's supposed to show metadata, and column A of sheet1 removes your duplicate data and only shows unique values? Column B shows the previous data? Or is it summarized based on unique values?

But it can be done.

In the case of deduplication summarization, there are several ways to implement (not limited to these methods):

Manually, copy column A of sheet2 into sheet1, then deduplicate with data-deduplication, and then summarize with the sumif function in column b.

Semi-manual operation. Use external data clerk ingestion and SQL statement, and use group by statement + sum summary function to summarize.

Use pivot tables. However, in terms of format, it may not be able to follow your format. However, the data in the pivot table can be copied. There is a wizard for pivot tables, which can be processed according to the wizard.

Writing VBA, there are more such methods. Dictionaries are generally the most efficient, followed by pure arrays, followed by loops. **Once written, it can be done with one click.

This is suitable for frequent use of this function in daily work. Once you've written it, you can get the result by pressing a button every time you put the data here. It's quick and easy.

It works very well for situations where there is a lot of repetitive work. Requires a private message.

Of course, there are other ways to do this. The main thing is to implement these features according to your own specific situation and requirements.