Course contents · Logical functions

Boolean logic and SUMPRODUCT

Open the practice in the trainer →

Boolean logic (after George Boole) reduces all values to TRUE or FALSE. Often it is easier to calculate a condition directly instead of using IF.

The condition =$B$4=H3 returns TRUE or FALSE. A double minus converts it into 1 or 0:

=--($B$4=H3)

SUMPRODUCT

Multiplies corresponding components of arrays and adds up the products. Combined with Boolean logic it is a universal “sum by condition”:

=SUMPRODUCT(--($A$2:$A$11=F2),$C$2:$C$11)

For each row: 1 × salary if the branch matches, 0 × salary otherwise. The sum is the branch payroll. Several conditions are simply multiplied:

=SUMPRODUCT(($A$2:$A$11=F2)*($B$2:$B$11="Manager")*$C$2:$C$11)

Tasks in the trainer

  1. In D2:D11 return 1 if the branch equals the value in F6, otherwise 0 — without IF, using a double minus.
  2. In G2:G4 — the payroll of each branch using SUMPRODUCT and Boolean logic.
  3. In H2:H4 — the payroll of the “Manager” position only, in each branch (two conditions).
  4. In I2:I4 count the employees of each branch with SUMPRODUCT and a double minus.
  5. In F13 — the total of salaries above 80,000 (a Boolean condition on the same column).

Solve the tasks in the trainer →

Check yourself

  1. What does =--(5>3) return?
  2. What does SUMPRODUCT({1;0;1},{10;20;30}) return?