Start a conversation ↗

Description

Excel — Conditional Functions: Testing, Counting and Calculating with Criteria

Three hours to write formulas that apply your business rules on their own

  • 0.43 days — 3 h
  • In-person or virtual
  • Intermediate
  • Up to 6 participants

To find out how many files are overdue in each department, people filter, count by hand and jot the result on a scrap of paper. Elsewhere in the same file, a conditional formula inherited from a former colleague stacks so many tests that no one dares touch it or check what it actually produces.

These 3 hours are designed for users who are already comfortable with basic formulas. Participants translate their business rules into logical tests, write simple then combined conditions, build multi-criteria counts and sums, and rework an existing nested formula to make it readable.

Learning objectives

  • Express a business rule as a logical test
  • Write a simple and then a nested conditional formula
  • Combine several conditions using logical operators
  • Produce counts and sums based on one or more criteria
  • Rework a conditional formula that has become unreadable in order to simplify it

What makes this programme different

Three real business rules translated into formulas during the session
An existing nested formula reworked through to a readable version
A multi-criteria counting table built step by step

Programme

1From business need to logical test

Writing the rule before the formula

  • Expressing a business rule as a set of conditions
  • Comparison operators and reference values
  • Result if true, result if false and unforeseen cases
  • Thresholds placed in a cell rather than inside the formula

2Simple and combined conditions

Stacking tests without getting lost

  • Conditional formula with a single test
  • Nesting and the order of successive tests
  • Combining conditions with AND and OR operators
  • Band-based treatment and lookup tables
  • Splitting into intermediate columns to stay readable

3Conditional counts and sums

Measuring against criteria

  • Counting against a single criterion
  • Conditional sums and averages
  • Multiple criteria and criteria ranges
  • Using wildcard characters
  • Checking totals by reconciling with the source table

Who is it for

Administrators, accounting assistants and department heads who produce figure-based reporting in spreadsheets.

Prerequisites

Regular use of a spreadsheet and the ability to write calculation formulas independently are required.

Dates & locations

12 scheduled dates between November 2026 and December 2027. Seats are confirmed in the order enquiries are received.

November 2026

December 2026

January 2027

February 2027

March 2027

April 2027

May 2027

June 2027

September 2027

October 2027

November 2027

December 2027

None of these dates suit you? We open additional sessions on request, and any programme can be run privately for your team.

Practical details

Before the programme
Online positioning questionnaire. Your development objectives are shared with the trainer, who tailors the practical case studies to your context.
Teaching methods
Theoretical input, workshops and practical case studies. Digital course materials and method sheets provided.
Assessment
Multiple-choice tests and role-play exercises. Assessment of learning at the start and end of the programme, with immediate and 60-day follow-up evaluations.
After the programme
One year of access to the e-learning platform. Self-assessment of the skills acquired and a 30-day follow-up session with your trainer.
How to register
Registration online or on the basis of a quotation.
Lead time
11 working days after confirmation of registration.
Accessibility
Accessible to people of determination. Contact our accessibility coordinator to design a suitable solution: contact@mpf-academy.ae
Start dates
Rolling intake: in addition to the scheduled sessions, this programme can start on request.