Detailed analysis of sumif and countif functions in Excel

Source: Internet
Author: User

As shown in:

E3 = countif (C2: C10, ">" & E2)-countif (C2: C10, "> =" & F2)
That is, use the number greater than 50 minus the number greater than or equal to 100 to get the location (50 ~ 100.

F3 = sum (countif (C2: C10, {"> 50", "> = 100"}) * {1,-1 })
(Countif (C2: C10, {"> 50", "> = 100"}) returns {6, 2} (array constant), followed by {6, 2} * {1, -1 }={ 6,-2}, sum {6,-2} = 4

G3 = sum (countif (C2: C10, ">" & {50,100}) * {1,-1 })
Similar to the F3 formula, only the & is used to connect strings. You must note that this formula is applied. If C2: C10 is 100, an error occurs.

G4 {= sum (countif (C2: C10, ">" & E2: F2) * {1,-1 })}
The G4 formula is similar to G3, which is represented by cells. The array formula {} is used here. In fact, the F3 and G3 should use the array formula because the arrays used cannot be found on the worksheet, but it seems that you can get the correct result even if you do not use it.

E4 = sumproduct (C2: C10> E2) * (C2: C10 <F2 ))
The E4 formula uses the sumproduct counting function to count the regions that meet the two conditions.

Bytes -----------------------------------------------------------------------------------------------
E8 = sumif (C2: C10, ">" & E7)-sumif (C2: C10, ">" & F7)
The formula of E8 is similar to that of E3, but sumif is used here, that is, sum of the satisfied regions.

E9 = sum (sumif (C2: C10, ">" & {50,100}) * {1,-1 })
E9 is similar to G3.

F8 {= sum (sumif (C2: C10, {">", ">="} & E7: F7) * {1,-1 })}
F8 is similar to G4.

E10 {= sum (C2: C10> E7) * (C2: C10 <F7) * C2: C10 )}
E10 uses the array formula to sum the regions that meet the two conditions.

F10 = sumproduct (C2: C10> E7) * (C2: C10 <F7) * C2: C10)
F10 is similar to E10, but here we use the sum function of the sumproduct function, so we can save the array formula {}

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: 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.