Подсчёт непустых клеток

[mik]

Ситуация: с помощью формулы

 =IF(B2>10,"1",IF(B2<10,"1*",""))

получаю в нужной клетке или 1 или 1* или пустоту (если условия не выполняется).

Проблема: нужно подсчитать количество непустых клеток содержащих такую формулу (фактически сумму всех единиц, но из-за необходимости отобразить 1* приходится превращать все единицы в текст).

sum() не работает из-за текстового значения в клетке
counta() реагирует на формулу в клетке и воспринимает её как непустую даже когда значение в клетке ""

Есть способ подсчитать непустые клетки содержащие текстовое значение чтобы при подсчёте исключались пустые текстовые значения "" ?
Может есть способ с помощью условного форматироавния приклеить к единице звёздочку * а саму единицу оставить числом чтобы при подсчёте пользоваться обычной sum(), поскольку при общем подсчёте звёздочка никак не учитывается?

[Pavel55]

А такая формула не подойдёт?

=СЧЁТЕСЛИ(C2:C30;"=1*")

где диапазон C2:C30 содержит вашу формулу, возвращающую 1, 1* или ""

[pashulka]

Подсчитать сумму всех единиц можно, например, так (см. ниже), к сожалению, при таком способе подсчёта будут учитываться не только "1", но и 1
=СЧЁТЕСЛИ(A2:A15;"1")
=COUNTIF(A2:A15,"1")

Если этот вариант неприемлем, то подсчитать количество "1" можно воспользовавшись любой из нижеопубликованных формул :

=СУММПРОИЗВ(Ч(A2:A12="1"))
=СУММПРОИЗВ((A2:A12="1")+0)
=СУММПРОИЗВ((A2:A12="1")*1)
=SUMPRODUCT(N(A2:A12="1"))
и т.д.

P.S. Для того, чтобы отобразить число(любое) со звёздочкой, можно выделить необходимый диапазон, затем в меню Формат выбрать команду Ячейки (CTRL+1), выбрать закладку Число, в списке Числовые форматы: выбрать (Все) и в поле Тип: ввести необходимый формат, например Основной"*" или Основной\*

[mik]

Pavel55: Спасибо. Это интересный вариант. Он позволяет отдельно подсчитать разные результаты (или 1 или 1* или ""). Мне же нужно подсчитать общее количество единиц и единиц со звёздочкой (1 и 1*) и игнорировать при прдсчёте клетки содержащие пустое значение ("").

Пояснение: пустое значение клетки ("") показывает отсутствие объекта, единица (1) определяет наличие объекта, а единица со звёздочкой (1*) помимо наличия объекта указывает на его особые свойства. Нужно подсчитать общее количество объектов с особыми свойствами или без них.

[mik]

pashulka:
=COUNTIF(A2:A15,"1") действительно учитывает все единицы, хранящиеся в ячейке как в численном (1) так и в текстовом формате ('1). Но, как и в варианте предложенном Pavel55 единицы со звёздочкой остаются неучтёнными.
С =SUMPRODUCT(N(A2:A12="1")) происходит то же самое.

Формулы
=СУММПРОИЗВ(Ч(A2:A12="1"))
=СУММПРОИЗВ((A2:A12="1")+0)
=СУММПРОИЗВ((A2:A12="1")*1)
если я правильно понимаю, призваны перевести единицы из текстового формата в численный. Опять же, "1*" останется неучтённой.

Форматирование клеток с помощью (Основной"*") или (Основной\*) не эфективно так как для каждой клетки это придётся делать вручную напрягая мозги и отслеживая нужно ли форматировать эту конкретную клетку или нет. И что в этом случае делать если значение в клетке изменилось?

Вобщем, проблему я конечно решил с помощью условного форматирования. Только вместо звёздочки (которой в условном форматировании нет) выбираю жирный фонт с подчёркиванием. А вместо =IF(B2>10,"1",IF(B2<10,"1*","")) поставил =IF(B2=10,"",1). И теперь весь подсчёт делаю с помощью =sum().

А есть способ в условное форматирование вставить свой тип формата чтобы добиться-таки отображения звёздочки?

[mik]

pashulka:
=COUNTIF(A2:A15,"1") действительно учитывает все единицы, хранящиеся в ячейке как в численном (1) так и в текстовом формате ('1). Но, как и в варианте предложенном Pavel55 единицы со звёздочкой остаются неучтёнными.
С =SUMPRODUCT(N(A2:A12="1")) происходит то же самое.

Формулы
=СУММПРОИЗВ(Ч(A2:A12="1"))
=СУММПРОИЗВ((A2:A12="1")+0)
=СУММПРОИЗВ((A2:A12="1")*1)
если я правильно понимаю, призваны перевести единицы из текстового формата в численный. Опять же, "1*" останется неучтённой.

Форматирование клеток с помощью (Основной"*") или (Основной\*) не эфективно так как для каждой клетки это придётся делать вручную напрягая мозги и отслеживая нужно ли форматировать эту конкретную клетку или нет. И что в этом случае делать если значение в клетке изменилось?

Вобщем, проблему я конечно решил с помощью условного форматирования. Только вместо звёздочки (которой в условном форматировании нет) выбираю жирный фонт с подчёркиванием. А вместо =IF(B2>10,"1",IF(B2<10,"1*","")) поставил =IF(B2=10,"",1). И теперь весь подсчёт делаю с помощью =sum().

А есть способ в условное форматирование вставить свой тип формата чтобы добиться-таки отображения звёздочки?

[pashulka]

Mik, Совершенно очевидно, что написанные мною формулы, не предназначены для суммирования "1" и "1*", т.е. налицо неверная трактовка поставленной задачи, впрочем, ничто не мешает следующий вариант :
=СУММ(СЧЁТЕСЛИ(A1:A4;{"1";"1~*"}))
=СЧЁТЕСЛИ(A1:A4;"1")+СЧЁТЕСЛИ(A1:A4 ;"1~*")
и т.д.
=SUM(COUNTIF(A1:A4,{"1","1~*"}))
=COUNTIFA1:A4,"1")+COUNTIF(A1:A4,"1 ~*")

Что касается создания пользовательского формата, то можно отформатировать сразу все необходимые ячейки, а при необходимости, и создать свой собственный формат, отличный от представленного. Условное форматирование - не предназначено для решения подобных задач.

[mik]

pashulka: =SUM(COUNTIF(A1:A4,{"1","1~*"}))

WOW! Интересный вариант! Я не знал, что можно использовать такой синтаксис. Спасибо.