10. sumif Function

I. Use the sumif Function

Sumif: sums cells that meet the conditions.

1. Syntax

= Sumif (range, criteria, [sum_range])

= Sumif (E: E, H8, F: F)

Calculate the value range

= Sumif (F: F, "& gt; 500", F: F)

If the number of characters exceeds 15 characters, the sumif function is used only for the first 15 characters.

= Sumif (A: A, F3 & "*", B: B)

Abbreviation of the third parameter

= Sumif (D: D, H4, F1) OR = sumif (D: D, H4, F1: F2)

Use the sumif function in multiple columns

= Sumif ($ A $1: $ I $11, L3, $ B $1)

Use secondary columns to process multi-condition sumif

First create a secondary column (= E2 & F2), = sumif (A: A, J5 & K5, G: G)

Ii. Use the sumifs function (Version 2003 does not have the sumifs function)

Sumfiis: sums the cells specified by a group of given conditions.

1. Syntax

= Sumifs (sum_range, criteria_range1, criteria1 ...)

= Sumifs (F: F, D: D, I5, E: E, J5)

Iii. Data Validity

Custom formula for Data Validity: = sumif (F: F, F6, G: G) <= sumif (A: A, F6, B: B)

10. sumif Function