EXcel table statistics, a name appears N times on the same day is counted once? 5

If you use VBA, it's simple, and the formula is a bit complicated, because you don't have row and column numbers in your screenshot, and I simulated a table myself, and if you can't, you will send the original file to.

Array formula: end with three keys.

If you use auxiliary columns or deduplication methods to solve the problem, you can use formulas to deal with it.

If it's deduplicated, then deduplicate by name and date, and then use countif to calculate.

If you use a formula, it's best to list both the 31 days and the name and then deal with it.

If you want to mold it all at once, you can use an array formula for a small number of people. But it will be more complicated, please send the original table to you to design the formula.

Using a VBA is certainly the best way to do it. Write it well and do it all the time. Complex formulas and ** are not free. It is necessary to have the original table, or the sample ** form. Write as a macro or a custom function. Events are also available.

If you still have questions, please feel free to continue to ask questions or send private messages.

The operation logic is clearer and easier to operate with VBA. But it can also be done with formulas, using array formulas.

sum(1 countifs(date area address, date area address, person name area address, person name condition))}

Press Ctrl+Shift+Enter at the same time when the formula is finished

Use a custom function or just use it.

Make a footprint and think about it in the afternoon.

Here's how1. First open WPS, select the data to be counted, and click to open the data function of the column of work and annihilation.

2. Then on the Data page, click Use Pivot Table.

Function. <>

3. Then, in the window that opens, select the location where the pivot table data is stored, and click OK.

4. After confirmation, check the brand in the field column staring table, and then add it to the "value".

5. After completing the setting, you can count the number of times each item appears.

Commonly used shortcuts.

Ctrl+1 cell formatting.

Ctrl+9 hides the row where the cell or region is located.

Ctrl+Shift+9 unhides rows in a cell or range.

Ctrl+0 hides the column where the cell or beater region is located.

Ctrl+Shift+0 unhides the column where the cell or range is located.

Ctrl+S to save the worksheet.

Ctrl+Shift+= Quickly insert cells.

Ctrl+X to cut.

Ctrl+C Copy.

Ctrl+V paste.

Ctrl+Shift+L filtering.

The content comes from the user: Letu data.

Chapter 1: The number of occurrences of the specified content in excel statistics excel counts the number of occurrences of the specified content in excel and a certain data is repeated, you need to count the number of occurrences of it, you can use the countif function to solve it directly, this article uses this function to count the number of occurrences. Method Step 1

Syntax: countif(range,criteria) where range represents the area to be counted as the number of non-empty cells, where criteria represents the condition defined in the form of numbers, expressions, or text 2Take this example to illustrate how to count the number of times "Zhao Si" appears.

3.Enter =countif(A2:A2,) in cell E2"Zhao Si"), (excel** counts the number of occurrences of all names) where a2:

a14 indicates the area of statistics, and Zhao Si needs to be in quotation marks to indicate the conditions to be counted. 4.After entering, the result is 3, which is the same as the number in the previous area.

Chapter 2: Find duplicate names in excel** Find duplicate names in excel**.

1. As long as it is repeated, fill the color Suppose the name you enter is in column A, select column A, format-conditional formatting, select the formula, enter the formula: =countif(a:a,a1)> 1 click on the "pattern" of the "format" below, set a fill color, (if it is only a change in the font color, select the "font" of the "format") as shown in the figure:

If there are duplicate names in column A, the color will be automatically filled, and the above setting is that as long as it is repeated, the color effect will be filled:

2. If it is repeated, the first data is not prompted, and the second duplicate is filled with color The formula is =countif(a$1:a1,a1)>1

There are two ways to do this:

1. If the person's name is out.

Now in the same column (such as column A) Select the column Data in the tool column Filter a drop-down arrow will appear in the column Click the drop-down arrow, which will appear the name of the person you need Click on the person's name to filter out the number of rows where this name appears, and right-click the "number" at the bottom of the excel file, and select the "count" (with header row) or "count" (without header row) in it to get the number of occurrences.

2. Use the countif function to enter the formula countif (a1:i14, Zhang San) in a blank cell, and then you can return the number of occurrences of the person's name you want to count in the blank cell. a1:

i14 is the range of all cells you want to find statistics.

There are two ways to do this:

1. If the person's name appears in the same column (such as column A) Select the data in the column Filter A drop-down arrow will appear in the column Click the drop-down arrow, and the name of the person you need to talk about will appear in it Click on the person's name, you can filter out the number of rows where this name appears, and right-click the "number" at the bottom of the excel file, and select the "count" (with header row) or "count" (without header row) or "count" (without header row) in it, you can get the number of occurrences.

2. Use the countif function.

Enter the formula countif(a1:i14,zhangsan) in a blank cell, and you can return the number of times you want to count the number of state names in the blank cell. A1:I14 is the range of all cells you want to find the flush meter.

Hope it helps!

Summary. Select these 3 columns, insert - pivot table - confirm, the column and column options will appear on the right, check the name of the row, check the date of the column (value), the general default statistical mode is count, if not, select the statistical mode as count on the right-click date column, so that you can count the number of times each person.

How to count each person with multiple names in excel at different times, and how many times a day?

Select these 3 columns,Insert-pivot table-confirm,Row and column options will appear on the right,Row check name,Column (value sensitive) check date,Generally, the default statistical mode is count,If not, select the statistical mode as count,So that Feng Sakura can count the number of times each person。

Which three columns? Select these 3 columns,Insert-pivot table-confirm,Row and column options will appear on the right,Row check name,Column (value sensitive) check date,Generally, the default statistical mode is count,If not, select the statistical mode as count,So that Feng Sakura can count the number of times each person。

I need to ask for one person right now, and there may be multiple times in the same time period, but I can only count it as one occurrence, and then there are many times in the same day.

Select these 3 columns,Insert-pivot table-confirm,Row and column options will appear on the right,Row check name,Column (value sensitive) check date,Generally, the default statistical mode is count,If not, select the statistical mode as count,So that Feng Sakura can count the number of times each person。

<> click Enter a description.

<> click Enter a description.

<> click Enter a description.

Please click to enter the description of the vibrillation tablets.

5.After that, the condition is entered, and the condition is the object we need to compare, which is the data in the first column, and then enter a2, and then use the comma of the half-width eggplant Yu Wei to separate it from the previous data, as shown in the figure.

<> click Enter a description.

6.When you're done, press Enter.

<> click Enter a description.

7.After that, select the first cell with a function, click the control point in the lower right corner, and then pull it downward, you can fill the rest of the cells, if our data is greater than 1, our data is duplicated, as shown in the figure.

<> click Enter a description.

First of all, open "Excel" on your computer, and open the ** we want to work on in it.

2. Take the data in column A in the ** as an example, count the number of occurrences of the cell content.

3. Select cell B2.

4. Copy and paste the formula "=CountIF(A$2:A8,A2)" into this cell.

5. Then press the enter key, and then the number of occurrences of the content of cell A2 will be displayed in cell B2.

6. Then, we select cell B2 again, move the mouse to the lower right corner of the cell until the mouse becomes a "+" sign, and then hold down the mouse to pull it down, so that we can get the data on the number of occurrences of the cell content in column B cells.

Count =countif($a$1:$c$48,"Name")

Name changes count the number of occurrences of different names.

1.Open it.

Data table, select the data to be counted, click "Insert", click "Pivot Table", such as special.

Figure below. 2.And then.

A page will appear, click "New Worksheet", and then click "OK", as shown below.

4.Drag and drop the "Affiliation" to the "Value" box to count how many times each name appears in the original table, as shown in the following figure.

10.You will find that the results are all sorted from the highest to the lowest number of occurrences, and the task is completed.

Use the countif function to count the number of times a value occurs in a certain area, and if there are five tables, you need to write five times, and you can add them up