Conditional functions: SUMIF or SUMIF and COUNTIF or COUNTIF

14/07/26 •0•0

Let's continue looking at Excel formulas, now let's try to understand SUMIF or SUMIF, COUNTIF or COUNTIF

Автор:Leon

ПеревелEditorial

ОтредактированоEditorial

COUNTIF Function

Counts the number of cells that match a specific criterion placed in the function.

Syntax of the COUNTIF function

=COUNTIF(range_to_count; criterion)

Where range to count is the range of cells that are the target and the criterion is a logical test (greater than something or less than something). The types of tests that exist:

Criterion	Operator	Example
Greater than	>	=COUNTIF(A2:A10; ">5")
Less than	<	=COUNTIF(A2:A10; "<10")
Equal to	= (can be omitted)	=COUNTIF(A2:A10; "="&D1) or =COUNTIF(A2:A10,D1)
Not equal to	<>	=COUNTIF(A2:A10; "<>"&D1)
Greater than or equal to	>=	=COUNTIF(A2:A10; ">=5")
Less than or equal to	<=	=COUNTIF(A2:A10; "<=10")

Example

1. Count the number of cells with apples in the cells

2. Count the number of cells with a value greater than 55

3. Count the number of cells with apples or oranges

Fruits	Quantity
Apples	32
Oranges	54
Peaches	75
apples	86

Solution

1. =COUNTIF(A2:A5 ; "=apples")

2. =COUNTIF(B2:B5;">55")

2. =COUNTIF(A2:A5 ; "=apples") + COUNTIF(A2:A5 ; "=oranges")

COUNTIFS Function

The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

Syntax of the COUNTIFS function

=COUNTIFS(range_to_count; criterion; [range_to_count2 ; criterion2; ... ])

Example

1. How many salespeople exceeded their quotas in Q1 and Q2?

2. Did Andrade exceed all sales quotas?

Salesperson	Q1 Quota Exceeded	Q2 Quota Exceeded	Q3 Quota Exceeded
Andrade	Yes	No	No
Barros	Yes	Yes	Yes
Gonçalves	Yes	Yes	Yes
Nuno	No	Yes	Yes

Solution

1. =COUNTIFS( B2:B5 ; "=Yes"; C2:C5 ; "=Yes")

2. =COUNTIFS(A2:A5 ; "=Andrade"; B2:B5 ; "=Yes"; C2:C5 ; "=Yes"; D2:D5 ; "=Yes"; )

SUMIF Function

The SUMIF function is the integration of the SUM function with the IF function. Use the SUMIF function to add the values in a range that meet the criteria you specify.

Syntax of the SUMIF function

=SUMIF( source_range; criterion; sum_range)

Where source_range and sum_range are lists of values and criterion is an expression.

Example

Vehicle	Sale Value	Commission
Mont	100000	7000
Baster	200000	14000
Nistrade	300000	21000
Keinar	400000	28000

Show the sum of the total commissions whose sale exceeded R$160,000.00.

Solution

=SUMIF(B4:B7;">160000"; C4:C8)

SUMIFS Function

The SUMIFS function, one of the math and trigonometry functions, adds all its arguments that meet multiple criteria.

Syntax of the SUMIFS function

=SUMIFS( sum_range; criteria_range1; criterion1; [criteria_range2; criterion2]; ... )

Example

Quantity Sold	Product	Seller
5	Apples	Diogo
4	Apples	Sara
15	Artichokes	Diogo
3	Artichokes	Sara
22	Bananas	Diogo
12	Bananas	Sara
10	Carrots	Diogo
33	Carrots	Sara

How many bananas did Diogo sell? How many other types of products did Diogo sell?

Solution

=SUMIFS(A2:A9 ; B2:B9 ; "=Bananas" ; C2:C9 ; "=Diogo")

=SUMIFS( A2:A9 ; B2:B9 ; "<>Bananas" ; C2:C9 ; "=Diogo")

Special Characters

Wildcards (* and ?) work only with text. For numbers, use comparators (>, <, etc.). Excel is not case-sensitive in COUNTIF.

Character	Name	What it does	Usage example	Expected result
	Asterisk	Represents any sequence of characters	"Ana"	Ana, Anabela, Anaconda
?	Question mark	Represents a single character	"A?a"	Ana, Aba, Axa
	Tilde (escape)	Allows searching for or ? literally	""	Finds text with *
<>	Not equal to	Excludes a specific value	"<>Ana"	Everything except Ana
=	Equal to (optional)	Explicit equality	"=Ana"	Only Ana

Name
Ana
Anabela
Bruno
Carlos
Ana Clara
Amanda
João
Axa
Aba

1) Count names that start with "Ana"

=COUNTIF(A1:A9;"Ana*")

2) Count names with exactly 3 letters starting with A

=COUNTIF(A1:A9;"A??")

3) Count names that are NOT Ana

=COUNTIF(A1:A9;"<>Ana")

4) Count names that contain "an" in any position

=COUNTIF(A1:A9;"an")

Exercises

1. You would like to sum the paid invoices. So you have a spreadsheet where in column A you put the customer’s name, in column B the invoice amount, and in column C the status, whether it was paid or not.

Customer	Amount	Status
Andréia	150	PG
Carlos	250
Patrícia	180	PG
Kate	350
Joana	250	PG
Eduardo	190	PG

Sum only the invoices that have already been paid.

Access here the spreadsheet that will be used for the next exercises

2. For each class, 2 periods are counted for a teacher. So, if a teacher gives 10 classes, they have a workload of 20 periods. How many periods does teacher Marta give?

3. How many class periods does teacher Calazans give to class 3C?

4. Ideally, a teacher gives several classes in a row in one day, thus spending less on transportation and wasting less time going to and from work. Divide teacher Josélia’s week by class periods. Is there any day of the week we should pay attention to?

5. How many periods did Girão give per class?

6. How many teachers are there in the spreadsheet (UNIQUE)?

Access here the spreadsheet that will be used for the next exercises

7. Create a "Gross Profit" column and calculate the total expected Gross Profit

8. Calculate the Total Gross Profit actually paid

9. Calculate the Gross Profit generated by the customer "Renata Tv Max"

10. How many times do customers that start with "Mar" appear?