How Excel counts by criteria and sums by criteria (such as monthly sums)

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.