☰

Сумма между двумя датами с несколькими текстовыми критериями

tag-icon • tag-icon microsoft-excel worksheet-function
Сумма между двумя датами с несколькими текстовыми критериями

Я хочу вычислить сумму (столбца Сумма в диапазоне U3:U8) по дате (столбец Дата в диапазоне S3:S8) в диапазоне от "1.4.18" до "31.3.19" только Амана, Делла и Ивана (столбец Описание в диапазоне T3:T8.

Сумма между двумя датами с несколькими текстовыми критериями

решение1

Если у вас есть Microsoft 365, вот одна формула, которая вам подойдет:

=SUM(
    FILTER(
        $C$3:$C$8,
        BYROW(
            $B$3:$B$8,
            LAMBDA(arr,
                OR(ISNUMBER(SEARCH(TEXTSPLIT(B12, ","), arr)))
            )
        ) * ($A$3:$A$8 >= $A$12) * ($A$3:$A$8 <= A13)
    )
)

Это можно сделать и в более ранних версиях, но это немного сложнее.

Сначала создайте дваИменованные формулы:

numWords Refers To: =LEN(Sheet9!$B$12)-LEN(SUBSTITUTE(Sheet9!$B$12,",",""))+1

seq      Refers To: =ROW(INDEX(Sheet9!$A:$A,1):INDEX(Sheet9!$A:$A,numWords))

Затем вы можете использовать эту формулу:

=SUM(
    MMULT(
        ISNUMBER(
            SEARCH(
                TRANSPOSE(
                    TRIM(
                        MID(
                            SUBSTITUTE(
                                $B$12,
                                ",",
                                REPT(" ", 99)
                            ),
                            IF(seq = 1, 1, (seq - 1) * 99),
                            99
                        )
                    )
                ),
                $B$3:$B$8
            )
        ) * ($A$3:$A$8 >= $A$12) * ($A$3:$A$8 <= $A$13),
        seq ^ 0
    ) * $C$3:$C$8
)

введите описание изображения здесь

Во второй формуле:

numWordsколичество слов в ячейке «Содержит слова»
seqвозвращает массив чисел, равный numWords (например: {1,2,3})
MMULTиспользуется для преобразования массивов допустимых поисков в один столбец значений (например, аналогично BYROWв первой формуле).

Чтобы разделить текстовую строку по запятым:(замена TEXTSPLITфункции

TRIM(
                        MID(
                            SUBSTITUTE(
                                $B$12,
                                ",",
                                REPT(" ", 99)
                            ),
                            IF(seq = 1, 1, (seq - 1) * 99),
                            99
                        )

Связанный контент