Using mode with several criteria

You’re essentially trying to calculate a mode with multiple criteria, which Excel doesn’t offer as a single built-in function — but there are clean and reliable ways to do it in Excel 365.

Here is the simplest way without VBA and Office Script.

BEST PRACTICE (Excel 365): FILTER + MODE.MULT

Since you’re on Office 365, you already have all the tools you need.

Concept

For each row in your monthly summary table, you:

1. Filter the original accident table by:

• BR
• KM
• Ano
• Mês

2. Calculate the mode of condicao_metereologica from the filtered rows.

Example structure

Assume your raw data table is named tblAcidentes and has columns:

• BR, KM, Ano, mes, condicao_metereologica

And your summary table row contains:

• [@BR], [@KM], [@Ano], [@Mês]

Formula (most common case: one mode)

=LET(

dados, FILTER(

        tblAcidentes[condicao_metereologica],

        (tblAcidentes[BR]=[@BR]) *

        (tblAcidentes[KM]=[@KM]) *

        (tblAcidentes[Ano]=[@Ano]) *

        (tblAcidentes[mes]=[@Mês])),

    INDEX(MODE.MULT(dados),1))

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.