Как в excel суммировать ячейки с цифрой и текстом?

Question

[maratmd]

Как в excel суммировать ячейки с цифрой и текстом?

т.е.
в ячейке G2 должна получиться сумма всех ячеек строки 2 где есть буква "л",

а в ячейке H2 сумма всех ячеек строки 2 где есть буква "п"

пример на скрине

Comments

[Антон Федорян]

формула или макрос?

[maratmd]

Антон Федорян, формула

[Saboteur]

IMHO формула не умеет считать цифры с буквами. Вам нужно хранить буквы и цифры в разных ячейках для формулы.

[maratmd]

Saboteur, а если подсчитать сумму только тех ячеек где есть любая буква. Если просто использовать сумм, то суммируются ячейки без текста, а как сделать наоборот, чтобы суммировались только ячейки которые содержат любую букву?

[Saboteur]

Число и строка - разные типы переменных. Формула не сможет суммировать.

[maratmd]

е мое

[Олег Беж]

Что мешает сделать так:
Л П None
8 2
3 9
6
2
И потом уже суммировать цифры в столбцах

[maratmd]

Олег Беж, к сожалению не вариант

Answer 1

[Bcoon]

Для буквы "л" формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("л";B4:G4);--ПОДСТАВИТЬ(B4:G4;"л";""));0))
Для "п" соответственно такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("п";B4:G4);--ПОДСТАВИТЬ(B4:G4;"п";""));0))

Обе - формулы массива. Вводятся одновременным нажатием клавиш Ctrl+Shift+Enter

Comments

[maratmd]

Отдуши, вообще супер, то что нужно. Большое спасибо!!!!!!!!!!!!

[maratmd]

Игорь, может еще поможете с одним вопросом, правда не знаю возможно ли вообще такое.

Суть в следующем. Для каждой ячейки хочу сделать выпадающий список с этими же значениями, например 8л;2п;3л и т.д

Как сделать чтобы в ячейке при выборе значения была видна только цифра? например я выбрал 8л из выпадающего списка, но в ячейке было видно только цифру 8, но при этом работала ваша формула

Для буквы "л" формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("л";B4:G4);--ПОДСТАВИТЬ(B4:G4;"л";""));0))
Для "п" соответственно такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("п";B4:G4);--ПОДСТАВИТЬ(B4:G4;"п";""));0))

Надеюсь понятно написал, что я хочу?

[Bcoon]

maratmd, думаю, тут не обойтись без макроса, который будет красить букву в цвет фона ячейки при выборе какого-то значения из списка. Таким образом, в списке будут цифры с буквами, а в ячейке видны только цифры (буквы будут окрашены в фоновый цвет). Других путей не вижу. Макрос надо поместить в модуль листа, диапазон замените на свой, в котором расположены ячейки со списками:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Integer
    If Not Intersect(Target, Range("D1:D20")) Is Nothing Then
        For n = 1 To Len(Target)
            If Not (IsNumeric(Mid(Target, n, 1))) Then
                Target.Characters(n, 1).Font.Color = Target.Interior.Color
            End If
        Next n
    End If
End Sub