How to count the number of different names in the second column that meet the conditions of the firs

1. First of all, we open excel, then we enter some numbers, then we select the cells in the diagram, and then click the formula;

2. Then click the drop-down arrow of automatic summing, and then click on other functions; This is important.

3. Then we set the selection type to all, then we find countif, then click to select it, and then click OK;

The formula you want: the sparrow.

Different days: sumproduct(($a$2:$a$9=e2) countifs($a$2:$a$9,$a$2:$a$9,$b$2:$b$9,$b$2:$b$9)).

The number of cherry blossoms is empty:

sumif(a:a,e2,c:c)

Name deduplication, just copy column A to column e, and then use the delete spine under the data tab to burn the duplicates.

<> enter or copy and paste the following calendar formula in D2.

countifs(a$2:a2,a2,b$2:b2,b2) drop-down fill. Enter or copy and paste the following formulas in F2 G2 respectively.

sumifs(d:d,a:a,e2,d:d,1)sumif(a:a,e2,c:c)

Select Hail F2:G2 and pull down to fill.

For this problem, it is best to use a pivot table, insert a pivot table, and check "Add some data to the data model" in the wizard, which is necessary for distinct counting later.

Drag the name to the row field, and the time and quantity to the value field area. The pivot table counts the time of the text field and sums the number of them. Now the count is still the total number of days.

Right-click any cell in the time column and select Distinct Count in the Context menu, and the count will be changed to Distinct Count. Modify the content of the title again, and it is the desired result.

Advantages: You only need to click the mouse to get the results, no formulas, and high efficiency.

Multi-condition to find the number of numbers with countifs or sumproduct functions and shirts.

Multi-condition summing is done with sumifs or sumproduct functions.

1. First of all, enter the data of gender and age in excel**, and you need to calculate the number of women between the ages of 15 and 49 according to the data.

2. Enter the function formula in cell C2: =sumproduct((a2:a7=.)"Female")*b2:b7>15)*(b2:b7<49))。

3. Click Enter to generate the calculation result, which shows that the number of people in the corresponding condition is "2 people".

4. If you need to calculate the number of people under other conditions, you can modify the calculation formula, for example, the gender is changed to "male" to get the number of males under the same condition as "3".

1. First of all, enter the data of gender and age in excel**, and calculate the number of women aged 15 to 49 according to the data.

2. Enter the function formula in the C2 unit genitive: =sumproduct((A2:A7=.)"Female")*(b2:b7>15)*(b2:b7<49))。

3. Click Enter to generate the calculation result, which shows that the number of people in the corresponding condition is "2 people".

4. If you need to calculate the number of people under other conditions, you can modify the calculation formula, for example, the gender is changed to "male" to get the number of males under the same condition as "3".

Material to Tool: Excel2010

to the corresponding column position. zhi

class="ikqb_img_alink">

2. Click the function button to insert the number of DAO functions. Select SumProduct in Mathematics & Trigonometry and click OK.

3. Pop up the function parameter setting box, note that the basic description of this function is to return the sum of the corresponding array or region to take the plane.

4. You need to write all the conditions into the input box of the first line, and the basic format is (condition 1) * (condition 2).

5. In order to make the formula automatically calculate the correct cells of the corresponding column in the right and downward dragging, you need to add the absolute reference $ reasonably.

6. When you finish the calculation, you will see the correct result. Drag the formula to the appropriate position in column i. The number of eligible men and women is calculated.

1. Take the Excel2010 version as an example, as shown in the figure below, how many women aged 15 to 49 should be counted;

2. First of all, enter the countifs function in cell C1, because there are multiple conditions to be counted here, so you can't use the countif function;

3. Enter the formula =countifs(a:a,"Female",b:b,">=15",b:b,"<=49"), press the Enter key to get the statistical result;

4. If you change the conditions to count the number of men between 20 and 49 years old, then you can change the parameters in the corresponding position of the formula.

=sumproduct(($a$2:$a$7="Female bai")*(b$2:$b$7>15)*(b$2:$b$7<49))

Suitable for any version of excel

If zhi wants to pack dao contains 15 years old and back.

A: 49 years old. sumproduct(($a$2:$a$7="Female")*(b$2:$b$7>=15)*(b$2:$b$7<=49))

Suppose column A and column B are two sets of data, and the number of data satisfying A=6 and B 60 is obtained.

Enter the formula "=sumproduct((a1:a13=6)*(b1:b13>60))" into the cell.

version of the desired result right.

This problem is solved by using the multi-condition summing of sumproduct.

Syntax: sumproduct (condition 1 * condition 2 * condition 3....Condition n) * Sum of all conditions.

1. Create a title row, select it, and filter it.

2. Delete the blank line with no data in the middle.

3. Screening, for age groups, can be used "between".

Find a space and fill in the formula =sumproduct(($c$9:$c$1009="Female"*(f$9:$f$1009=18)*1), the result is as shown in the figure.

“=sumproduct((c9:c1009="Female"*(t9:t1009=18)), apply to the source formula and the data list in the same worksheet, and note the " to the right of the formula equal sign"Female"and "18" must be consistent with the list. If the list is "18 years old", the formula must be "."18 years old"instead of "18".

When the list refers to the address of other tables, you should place the cursor at the formula reference and use the cursor to select the reference range of other tables.

Assuming the data is in line 3.

sum(countif(c3:n3,))

Press Ctrl+Shift first, and press Enter at the end.

1. First of all, enter two sets of data in Excel**, and you need to calculate the sum of the corresponding data according to the date.

2. Select the cell answer area and click "Pivot Table" in the "Insert" tab.

3. Then drag "Date" into "Filter", and drag "Data" into the window of "Value".

4. Then click the drop-down menu in the "Date" field to select the date you want to sum up.

5. For example, if you select August 1, you can display the sum of the data corresponding to August 1 in the summation item data.

Directly in column C, e.g. single bai lattice duc1, use the formula =sumif(a:a,a1,b:b).

Then the formula is pulled down, zhi.

If you add up DAOs, there are no duplicates.

The number of weights for the same day can be found in cell C1

Enter the date, then scroll down to autofill the date.

Then in cell d1, use the formula.

sumif(a:a,c1,b:b)

Scroll down the formula, and you're done.

Enter or complex in d1.

Paste the following formula.

Pull-down padding. Or copy column A and paste it into.

Select Column D to delete.

is a duplicate. Enter or copy and paste the following formula in e1.

sumif(a:a,c1,b:b)

Pull-down padding. Or use pivot tables.

Drag Date to Row Area Drag the quantity to the numeric area.

I don't know how to filter another column if you don't know how to filter one column.

It's too general.

You can use the sumif function.

Use functions and statements to count.

You can use the sumif function or an array formula to find the sum of two more than two conditions for another column of data.

<> note that after entering the formula (without entering curly braces), you need to press Ctrl + Shift + Enter to finish.

=sumproduct((a2:a1000="Zhang San")*b2:b1000="Mathematics")*c2:c1000)

If the above formula is calculated to be 0, then you can see what your data looks like in the previous figure, whether it is in the three columns a, b, and c.

Column A name is not"Zhang San", column b is there"Mathematics"Accounts, whether there is a numerical value in column C.

Use sumifs multi-conditional summation function (excel2007), =sumifs(c:c,a:a,"Zhang San",b:

b,"Mathematics"If there are other things that need to be counted, you can set the conditions in different columns, and the "Zhang San" and "mathematics" in the formula can directly refer to the cells used for the conditional values, and this formula can set more than 100 conditions at most, which is absolutely enough.

Sum the conditions... The condition can be used with an if function.

The if function can meet 7 conditions.

=sumproduct((a2:a100="Zhang San")*b2:b100="Mathematics")*c2:c100))

Note: Column C starts from the first row of numbers, without the header, if the header is in the third row (A3:C3), then A2, B2, and C2 in the above formula should be changed to A4, B4, C4;

In addition, the data in column C must be numeric and cannot be textual.

This is where the pivot table comes in. Because it is a two-level classification summary, it cannot be achieved simply by using formulas and combined calculations.

Select "Insert" -- select the pivot table on the far left --- select the selection area, note that you want to select them together with the header, that is, write these cells of "name, subject, score", --- OK--- select all three items in the right side of "Select fields to add to the report", and it's OK.

The default is to reconcile, and you can also count and average based on this. For example: How many times did Zhang San take the Chinese exam? What is the average city of his language? and so on, these questions can be easily answered.