Multi-condition statistics in Excel (summary sharing)

This article brings you relevant knowledge about excel, which mainly organizes issues related to multi-condition statistics, including multi-condition judgment of IF function, multi-condition summation of SUMIF, and multi-condition SUMIFS Let’s take a look at conditional summation and so on. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

1. IF function multi-condition judgment

Requirements: If department There are low-temperature subsidies for production and job-based operations.

Formula:

=IF(AND(B2="Production",C2="Main Operator"),"Yes","No")

AND function judges two conditions. If they are met at the same time, the IF function returns "yes", otherwise it is none.

2. SUMIF multi-condition summation

Requirement: Statistics of the total year-end bonuses of the two departments in cells E2 and E3

Formula:

=SUMPRODUCT(SUMIF(B2:B9,E2:E3,C2:C9))

The SUMIF function summation condition uses E2:E3 to obtain respectively The year-end bonuses of the two departments are then summed using the SUMPRODUCT function.

3. SUMIFS multi-condition summation

Requirements: The statistics department is production and the position is the main subsidy total amount

Formula:

=SUMIFS(D2:D9,B2:B9,F2,C2:C9,G2)

SUMIFS function summing area is D2:D9, sum The conditions are B2:B9=F2 and C2:C9=G2

4, multi-condition counting

Requirements: The statistics department is production, and the number of people in the position of master operator

Formula:

=COUNTIFS(B2:B9,F2,C2:C9,G2)

##5. Multi-condition calculation Average

Requirements: The statistics department is production and the position is the average year-end bonus

Formula:

=AVERAGEIFS(D2:D9,B2 :B9,F2,C2:C9,G2)

The first parameter is the numerical area to be counted, followed by the paired condition areas and the specified conditions. .

6. Multi-condition search

Requirement: The query department is production and the position is the name of the director

Formula:

=LOOKUP( 1,0/(B2:B9=F2)/(C2:C9=G2),A2:A9)

LOOKUP function multi-condition query routine is:

=LOOKUP(1,0/(condition)/(condition 2)/(condition n), query area)

If you are using Office 365 or the 2021 version of WPS form , you can also use the XLOOKUP function to complete multi-condition queries.

=XLOOKUP(F2&G2,B2:B9&C2:C9,A2:A9)

##The first parameter of the XLOOKUP function is the search content, and the second parameter is the query Range, the third parameter is the cell range whose content is to be returned.

Related learning recommendations:

excel tutorial

The above is the detailed content of Multi-condition statistics in Excel (summary sharing). For more information, please follow other related articles on the PHP Chinese website!