How to count cells that only contain the VALUE 0 and not the result of a formula?

excel excel-formula formula countif
Ana Ban picture Ana Ban · Apr 20, 2013 · Viewed 40.3k times · Source

For the following sheet:

    A
1   0
2   0 as formula result
3   0
4   0 as formula result
5   0 as formula result
6   blank
7   0

How do I count only the cells with 0 entered as VALUES and not the cells with 0 as their formula result, i.e.

COUNTIF(A1:A7,0 AS VALUE) = 3

I've tried the following:

COUNTIF(A1:A7,0) = 6

COUNTIF(A1:A7,"0") = 6

Answer

pata picture pata · Jan 28, 2015

COUNTIF(A1:A7;0) this worked for me

Related questions

Simulate string split function in Excel formula

I am trying to split a string in an excel formula, something like I can do in many programming languages, e.g. string words = "some text".split(' '); The problem is that I can't be sure that there is …

Read More
Retrieving Values From Excel Merged Columns

I'm doing a hlookup against a value that spans multiple columns. My data is similar to this: A B C D --------------------------- 1| Col1 Col2 2| x y z w 3| 4| In rows 3 and 4 (A3, B3, C3, D3, etc.), I'd like to put …

Read More
Count Unique values with a condition

In column A I have list of different names. In column B, I have values either 0 or 1. I want to get a count of all the unique names from column A which have 1 in column B. Using below array formula …

Read More