Excel: как суммировать, если ячейки содержат текст
Вы можете использовать следующие методы для суммирования ячеек в Excel, содержащих определенный текст:
Метод 1: суммирование ячеек, содержащих один конкретный текст
=SUMIF( A2:A11 , "\*avs\*", B2:B11 ) Эта формула суммирует ячейки в диапазоне B2:B11 , где соответствующие ячейки в A2:A11 содержат «avs» в ячейке.
Метод 2: суммирование ячеек, содержащих один из нескольких текстов
=SUMIF( A2:A11 , "\*avs\*", B2:B11 )+SUMIF( A2:A11 , "\*eat\*", B2:B11 ) Эта формула суммирует ячейки в диапазоне B2:B11 , где соответствующие ячейки в A2:A11 содержат «avs» или «eat» в ячейке.
В следующих примерах показано, как использовать каждый метод на практике со следующим набором данных в Excel:
Пример 1: сумма ячеек, содержащих один конкретный текст
Мы можем использовать следующую формулу для суммирования значений в столбце « Очки », где название команды содержит «avs» в названии:
=SUMIF( A2:A11 , "\*avs\*", B2:B11 ) На следующем снимке экрана показано, как использовать эту формулу на практике:
Мы видим, что ячейки, в которых столбец « Команда » содержит «avs», набрали в общей сложности 52 балла.
Мы можем проверить это вручную, просуммировав значения, где столбец Team содержит «avs»:
Сумма Очков Команд, в названии которых есть «avs»: 12 + 5 + 8 + 10 + 17 = 52 .
Пример 2. Суммарные ячейки, содержащие один из нескольких текстов
Мы можем использовать следующую формулу для суммирования значений в столбце « Очки », где название команды содержит «avs» или «eat» в названии:
=SUMIF( A2:A11 , "\*avs\*", B2:B11 )+SUMIF( A2:A11 , "\*eat\*", B2:B11 ) На следующем снимке экрана показано, как использовать эту формулу на практике:
Мы видим, что ячейки, в которых столбец Team содержит «avs» или «eat», набрали в общей сложности 65 баллов.
Мы можем проверить это вручную, просуммировав значения, где столбец Team содержит «avs» или «eat»:
Сумма Очков для Команд, в названии которых есть «avs» или «eat»: 12 + 5 + 8 + 10 + 13 + 17 = 65 .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как суммировать текст в ячейках в excel
Автор: Сергей. Здравствуйте, имеется таблица для расчета общей стоимости работ за месяц. Первый столбец имена, первая строка даты (за месяц). В ячейках есть как числа так и текст. Как суммировать все часы за месяц включая ячейки с текстом (каждому тексту соответствует определенное количество часов, указанное в легенде снизу)? Так же есть ячейки в которых и текст и число, соответственно нужно суммировать и текст и число по отдельности. Спасибо!
Изменен: 29.06.2017
Читать сообщение на форуме или сайте.
. » очень серьезно. Код мой, с моего сайта. И чтобы сделать . по проверке критерия и сцепления ячеек. По сути функция такая тоже . взяли приведенную выше функцию по суммированию с нескольких листов: Как сцепить несколько . по критерию? СцепитьЕсли поместить вместе с приведенным кодом и можно попробовать . не забудьте для ячеек с функцией назначить в формате ячеек переносы строк — иначе . и будет выглядеть как слитный текст.
Изменен: 16.11.2022
Читать сообщение на форуме или сайте.
Автор: serGadzilla. Мне нужно получить число — сколько ячеек с n с жёлтой заливкой, посмотрите файл.
Изменен: 12.07.2011
Читать сообщение на форуме или сайте.
. : AiKiDANCER. Добрый день! Есть столбец с тремя видами валют. Суммы по . разным видам валют разделяю с помощью значения из соседнего столбца . ). Раздельное суммирование каждой валюты происходит по формуле (сумма всех ячеек в долларах . ! в некоторых ячейках имеется текст (просто текст без цифр). В этом случае . игнорировать ячейки с текстом (за одно и с нулевым значением)? Текст может быть фиксированным .
Изменен: 10.04.2014
Читать сообщение на форуме или сайте.
Автор: ivan.k. Уважаемые эксперты, помогите, я уперся в довольно тривиальную задачу, решение которой никак не получается найти ) В приложенном файле ячейка T9 должна суммировать заданные ячейки с временами. Эти ячейки нельзя выделить как диапазон, т.к. они следуют не подряд, а через одну. Также содержимое этих ячеек образуется посредством формул, которые содержат условие Еслиошибка «». Вот из за этих кавычек посчитать содержимое ячеек не представляется возможным. Кавычки заменять на 0 .
Изменен: 20.11.2022
Читать сообщение на форуме или сайте.
Автор: Jonsnow. Помогите определить или распознать отношение одной ячейки к другой. Есть столбец с текстом КС в нем две нумерации простые числа и десятичные КС2. КС2.1. КС2.6 (B;C). Есть столбец с текстом "а,в,с" (F). Есть столбцы с цифрами 1,75 и 12,8 (Н; I) 1 Мне необходимо определить что КС2 и 2 или одна строки ниже это один и тот же КС 2 чтобы десятичные числа которые ниже суммировались к целому числу КС к "с" т.е пример:КС2.1 (0,1 и 1) .
Изменен: 16.05.2019
Читать сообщение на форуме или сайте.
Автор: ReaderRider. Здравствуйте, нужно сложить отдельно жёлтые ячейки по условию «Пол. раб.» и отдельно голубые ячейки по условию «Кам. раб.» Формула СУММЕСЛИМН(C1:C24;A1:A24;»Пол. раб.») суммирует числа напротив ячейки с текстом, можно ли сделать суммирование со сдвигом на одну ячейку вниз?
Изменен: 25.04.2020
Читать сообщение на форуме или сайте.
Автор: vikttur. Не нужно смешивать текст и числовые значения. Суммируем, как обычно. Ячейкам задать формат 0,00 » руб.» (Формат ячеек — Все форматы) или выбрать нужный Денежный
Изменен: 20.02.2021
Читать сообщение на форуме или сайте.
Автор: evghtcone. БМВ, К сожалению, формула что была вами предложена — не работает. А суммирование комментариев идет по каждому столбцу по отдельности и нужно только сопоставить с нужным днем недели.
Изменен: 24.02.2020
Читать сообщение на форуме или сайте.
Автор: Андрей-1821. Добрый день. Подскажите как написать формулу чтобы она считала из суммируемых ячеек только те ячейки, в которых числа. Вот допустим у меня формула выглядит так: =А1+В1+С1. Где в ячейке А1 и С1 числа, а в ячейке В1 текст. Помогите пожалуйста исправить формулу. И ещё один вопрос как сделать так, чтобы если в одной ячейке написано «Авария», то в другой ячейке ставится цифра 100, если нет то слово «Нет». Заранее благодарю кто сможет уделить минутку
Изменен: 03.12.2019
Читать сообщение на форуме или сайте.
Автор: Дмитрий(The_Prist) Щербаков. Почему же нет. Костыли можно приделать. Правая кнопка мыши на листе «функция» -Посмотреть код(или Исходный текст) -вставляете туда следующий код:Private Sub Worksheet_Activate() Me.Calculate End Sub
Изменен: 15.02.2022
Читать сообщение на форуме или сайте.
Автор: dem.on87. Добрый день. Помогите суммировать ячейки с текстом. Сначала в ячейке идет общая сумма а потом суммы которые указывают куда были потрачены деньги. Все это надо суммировать и в этой же ячейке с общей суммой выпадающим списком (или другим способом) указать все эти пояснения без общих сумм. Можно не одной формулой, главное чтобы потом в желтых ячейках был результат со списком всех расходов. Очень много магазинов с такими расходами, если вручную все делать то дня 3 занимает. Буду .
Изменен: 15.06.2017
Читать сообщение на форуме или сайте.
Автор: gling. «Есть ли мысли как исправить? » Не разбирался в тонкостях формулы, но попробуйте МЕСЯЦ заменить на ТЕКСТ, типа =СУММПРОИЗВ(($A$13:$A$28=$B7)*(ТЕКСТ($G$11:$M$11;»ММГГ»)=ТЕКСТ(G$2;»ММГГ»))*$G$13:$M$28)
Изменен: 21.07.2015
Читать сообщение на форуме или сайте.
Автор: JayBhagavan. YANA22, зачем столько текста, когда проще в файле показать вручную как надо.
Изменен: 03.09.2015
Читать сообщение на форуме или сайте.
Автор: Sergun1. Прошу прощения, ещё один вопрос по этой теме, нужно теперь не две ячейки суммировать, а тридцать, как это сделать? Не плюсовать же в формуле большие куски текста.
Изменен: 18.06.2012
Читать сообщение на форуме или сайте.
Автор: Igor.Shkurenko.
Посмотрите, плиз, пример во вложении Файл удален — велик размер — [МОДЕРАТОРЫ]
Владимир, я так понял, что необходимо суммировать только то, что не буквы. Т.е. только цифры ВНУТРИ ячеек, а не ячейки с текстом, как критерий.
Изменен: 26.10.2010
Читать сообщение на форуме или сайте.
. диапазона по цвету SumByColor (Диапазон суммирования;Цвет). Подскажите, пожалуйста, формулу для . D5) сосчитала количество ячеек с любым текстом в столбце HZ с привязкой к желтому .
Изменен: 15.07.2019
Читать сообщение на форуме или сайте.
Автор: UNikolai. =ЕСЛИ(«1.1″=»»;»»;СУММЕСЛИ(<"1.1":"1.1":"1.20":"1.12">;»1.1″;)) =ЕСЛИ([@5]=»»;»»;СУММЕСЛИ([№_пр];[@5];[Q_общ*доб])) вот такая у меня есть формула и равна она 385! а есть еще вот такая( все в умной таблице)=ЕСЛИ(«1.20″=»»;»»;СУММЕСЛИ(<"1.1":"1.1":"1.20":"1.12">;& .
Изменен: 11.06.2020
Читать сообщение на форуме или сайте.
. то результат 49428,75 Ручное суммирование (выделением мышкой) показывает, что не . если их использовать в формуле с другими числами (например 27586,65 . -то надмозг экселя считает число текстом, то это число не будет . так. Как эффективнее всего бороться с такой проблемой? Может есть какой . не просто выделены? Или индикация ячеек с невалидными числовыми значениями, которые надо . нажав ENTER то числа из ячеек начинают считаться корректно. Изменение формата .
Изменен: 25.04.2019
Читать сообщение на форуме или сайте.
Автор: Дмитрий(The_Prist) Щербаков. Одно из решений отсюда должно подойти: Как просуммировать данные с нескольких листов, в том числе по условию Например, для ячейки С8 листа Январь вполне подойдет такая формула: =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(<"01.01":"02.01":"03.01">&"!B3:B100");B8;ДВССЫЛ(<"01.01":"02.01":"03.01">&"!C3:C100"))) .
Изменен: 27.12.2018
Читать сообщение на форуме или сайте.
Автор: Ivan010. Здравствуйте! В строке чередуются текст и числа. Требуется суммировать первые пять чисел в строке, в независимости от количества текстосодержащих ячеек. С уважением, Иван.
Изменен: 12.02.2014
Читать сообщение на форуме или сайте.
Автор: The_Prist. Надстройка для суммирования ячеек по цвету заливки или шрифта . добросовестно читаете справку. Для подсчета ячеек в функции CountCellColor предусмотрена возможность . подсчитать только ячейки либо с определенным текстом/числом, либо с любым значением. В . себе эти функции: Подсчитать сумму ячеек по цвету заливки Подсчитать сумму . ячеек по цвету шрифта После чего . по цвету и применяете функции с параметром считать только видимые ячейки .
Изменен: 19.02.2016
Читать сообщение на форуме или сайте.
Автор: evgeniygeo. Людмила Горчакова, а ошибки Вас не смущают, в которых по сути все написано? Вы по-прежнему хотите просуммировать текст. Есть вариант с дополнительным столбцом, в котором все значения переделать в число и после просуммировать данный столбец. =—Q2Либо выделить одну из ячеек с ошибкой, нажать ctrl+пробел и преобразовать в число
Изменен: 21.04.2021
Читать сообщение на форуме или сайте.
. к примеру с а1 по а99. Необходимо посчитать сколько ячеек содержат текст & . ;quot;яяя". Все бы хорошо,но часть ячеек . объединена и при суммировании учитывается только первая . поле состоит из 10 объединенных ячеек,то вывелось бы 10. Объединение .
Изменен: 09.02.2016
Читать сообщение на форуме или сайте.
Автор: Артём Кремлёв. День добрый. Помогите разобраться в формуле.=СУММ(ИНДЕКС(‘C:\Users\ххх\Desktop\[книга1.xlsb]Производство’!$I$49:$DD$49;1;ПОИСКПОЗ(ЗНАЧЕН(ЛЕВСИМВ(AL6;4));’C:\Users\ххх\Desktop\[книга1.xlsb]Производство’!$I$6:$DD$6;0)):ИНДЕКС(‘C:\Users\ххх\Desktop\[книга1.xlsb]Производство’!$I$49:$DD$49;1;ПОИСКПОЗ(ЗНАЧЕН(ЛЕВСИМВ(AL6;4)+2);’C:\Users\ххх\Desktop\[книга1.xlsb]Производство’!$I$6:$DD$6;0))) В данном случае при открытой книге1 формула работает .
Изменен: 01.11.2019
Читать сообщение на форуме или сайте.
Автор: hoho.hoho. oakrasikov, с синтаксисом в цикле, по идее, всё в порядке. Единственное — когда пишу For i As Integer 3 to 19 (то есть определяю переменную в условии, а не перед ним), то вижуал бейсик ругается (см. скрин). http://s019.radikal.ru/i631/1701/f9/aeb8c52cf670.jpg Что насчёт работы программы, то вот, это лист "Сделки". Ячейка F7 без цвета, текст в O7 черный. Значение ячейки N7 (7000) нужно приплюсовать к общей сумме. Если ячейка F имеет цвет или текст ячейки О .
Изменен: 17.01.2017
Читать сообщение на форуме или сайте.
Автор: vdovin_sg. Добрый день, Вопрос состоит в следующем: Есть две вкладки Лист1 и Лист 2. В Листе 1 указаны исходные данные, а в Листе 2 надо вычислить количество данных. Лист1: Дата — записываются данные по дате 31.01.2011 и т.д. по дням. Данные — в основном числа, но есть и текстовые данные (или текст+цифры) Условие, там находится какой-либо текст (неважно) и некоторые ячейки с текстом «пустой» Лист 2: В столбец Дата вносится вручную дата 31.01.2011 и т.д. В столбце& .
Изменен: 30.09.2016
Читать сообщение на форуме или сайте.
Автор: Юрий М. Только следите, чтобы в исходных ячейках не было явного текста. Текст с числами и слэшем не в счёт ))
Изменен: 24.06.2016
Читать сообщение на форуме или сайте.
Автор: Елена. Оригинальный способ сортировки по заданному столбцу по одному цвету (формату) Выделяем столбец,копируем его формат в любой свободный столбец,например инструментом Формат по образцу (очень советую вынести эту кнопочку на панель инструментов,находится в категории Формат), далее идем Правка—Заменить—кнопка Параметры—в строке Найти нажимаем кнопку Формат—выбираем цвет или заливку; или нажимаем треугольничек кнопки Формат и выбираем Выбрать формат из ячейки (если .
Изменен: 08.05.2012
Читать сообщение на форуме или сайте.
Автор: Guest. Есть два листа эксель. На первом листе есть список наименований и суммы. Наименования не повторяются. На втором листе есть тоже список, но в ячейках не просто наименования, а текст, в котором встречается одно из наименований с первого листа или вообще не встречается. Таких ячеек может быть много. Каждой ячейке соответствует какая-то сумма. Каждая строка имеет определенный код. Вопрос в следующем: можно ли какой-нибудь формулой: находить наименования с листа .
Как в excel суммировать ячейки с цифрой и текстом?
пример на скрине
- Вопрос задан более трёх лет назад
- 10273 просмотра
8 комментариев
Простой 8 комментариев
формула или макрос?
maratmd @maratmd Автор вопроса
Saboteur @saboteur_kiev
IMHO формула не умеет считать цифры с буквами. Вам нужно хранить буквы и цифры в разных ячейках для формулы.
maratmd @maratmd Автор вопроса
Saboteur, а если подсчитать сумму только тех ячеек где есть любая буква. Если просто использовать сумм, то суммируются ячейки без текста, а как сделать наоборот, чтобы суммировались только ячейки которые содержат любую букву?
Saboteur @saboteur_kiev
Число и строка — разные типы переменных. Формула не сможет суммировать.
maratmd @maratmd Автор вопроса
Что мешает сделать так:
Л П None
8 2
3 9
6
2
И потом уже суммировать цифры в столбцах
maratmd @maratmd Автор вопроса
Олег Беж, к сожалению не вариант
Решения вопроса 1
Bcoon @Volounteer
Для буквы «л» формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«л»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»л»;»»));0))
Для «п» соответственно такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«п»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»п»;»»));0))
Обе — формулы массива. Вводятся одновременным нажатием клавиш Ctrl+Shift+Enter
Ответ написан более трёх лет назад
Нравится 4 3 комментария
maratmd @maratmd Автор вопроса
Отдуши, вообще супер, то что нужно. Большое спасибо.
maratmd @maratmd Автор вопроса
Игорь, может еще поможете с одним вопросом, правда не знаю возможно ли вообще такое.
Суть в следующем. Для каждой ячейки хочу сделать выпадающий список с этими же значениями, например 8л;2п;3л и т.д
Как сделать чтобы в ячейке при выборе значения была видна только цифра? например я выбрал 8л из выпадающего списка, но в ячейке было видно только цифру 8, но при этом работала ваша формула
Для буквы «л» формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«л»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»л»;»»));0))
Для «п» соответственно такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«п»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»п»;»»));0))
Надеюсь понятно написал, что я хочу?
Bcoon @Volounteer
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СуммаЯчеек_Формат
Функция суммирует ячейки по их формату(правая кнопка мыши на ячейке —Формат ячеек (Format cells) -вкладка Число (Number) ). К примеру: имеются данные по каким-либо финансовым операциям(продажи, суммы заключенных договоров, прибыль и т.д. и т.п.). И данные эти записаны по разному: где-то это формат долларов, где-то формат рублей, где-то гривны. А надо просуммировать отдельно гривны, отдельно доллары и отдельно рубли:
В Excel нет функции, которая бы суммировала данные, различая формат ячейки. Вот тогда-то и может пригодится эта функция.
Вызов команды через стандартный диалог:
Мастер функций—Категория «MulTEx»— СуммаЯчеек_Формат
Вызов с панели MulTEx:
Сумма/Поиск/Функции — Математические — СуммаЯчеек_Формат
Синтаксис:
=СуммаЯчеек_Формат( $E$2:$E$20 ; J8 ; I8 ; $A$2:$A$20 )
=СуммаЯчеек_Формат( $E$2:$E$20 ; J8 )
=СуммаЯчеек_Формат( $E$2:$E$20 ; J8 ; I8 )
ДиапазонСуммирования( $E$2:$E$20 ) — диапазон значений для суммирования. Можно указать несколько столбцов. Столбец с критерием(если планируется суммировать еще и по критерию) не обязательно должен входит в диапазон. Именно в этом диапазоне ищутся совпадения формата ячейки с указанным.
ЯчейкаОбразец( J8 ) — ячейка-образец формата. Ссылка на ячейку с форматом (ячейки именно с таким же форматом будут просуммированы).
Критерий( I8 ) — необязательный аргумент. Если указан, то суммируются ячейки с указанным критерием и форматом ячейки. Допускается применение в критерии символов подстановки — «*» и «?» . Если не указан, то суммируются все ячейки, к которым применен указанный формат. Если в диапазоне суммирования находятся ячейки с текстом, то они будут игнорироваться.
Так же данный аргумент может принимать в качестве критерия символы сравнения ( , =, <>, ):
- «>0» — будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых больше нуля;
- «>=2» — будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых больше или равно двум;
- » — будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых меньше нуля;
- » — будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых меньше или равно 60;
- «<>0″ — будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых не равно нулю;
- «<>» — будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых не пустые;
- «*отчет*» — будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых содержит слово «отчет»;
Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: «<>«&D$1
ДиапазонКритерия( $A$2:$A$20 ) — Необязательный аргумент. Указывается диапазон, в котором следует искать критерий(если критерий указан). ДиапазонКритерия должен быть равен по количеству ячеек ДиапазонуСуммирования. Если ДиапазонКритерия не указан, то критерий просматривается в ДиапазонеСуммирования.
Важно: Функция не вычисляется при изменении формата. Для пересчета функции после изменения параметров необходимо выделить ячейку и нажать F2 — Enter . Либо нажать сочетания клавиш Shift + F9 (пересчет функций активного листа) или клавишу F9 (пересчет функций всей книги).
Примечание: данная функция не учитывает ячейки, к котjрым применено Условное Форматирование. Будет определен тот формат, который установлен через Формат ячеек стандартными средствами.
Расскажи друзьям, если статья оказалась полезной:
Видеоинструкции по использованию надстройки MulTEx
Поделитесь своим мнением
Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум