Detailed analysis of sumif and countif functions in Excel

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 {}