1.Usesumproductmake a multi-conditional count
Syntax:
=Sumproduct ((conditions1) * (conditions2) * (conditions3) *... ..(conditionsN))
function:
statistics at the same time meet the conditions1, Conditions2to the conditionNthe number of records.
Example:
=sumproduct (a2:a10= "male") * (b2:b10=")Intermediate Title"))
Formula Explanation:
number of employees who have sex as a male and have an intermediate title
2.Usesumproductmake multiple conditional sums
Syntax:
=Sumproduct ((conditions1) * (conditions2) * (conditions3) *... ..(conditionsN) *an area)
function:
summarize and meet the conditions1, Conditions2to the conditionNrecord the total amount of the specified area.
Example:
=sumproduct (a2:a10= "male") * (b2:b10=")Intermediate Title") *c2:c10)
Formula Explanation:
the sum of the wages of employees who are male in gender and who are titles of intermediate titles (assumingClisted as salary)
3. Sum by month and year
Grammar:
=sumproduct ((MONTH ($B $ $B $579) =1) * (Year ($B $ $B $579) =2008) *e$2:e$579)
Where 1 is the month and 2008 is the year.
How Excel counts by criteria and sums by criteria (such as monthly sums)