countifmicrosoft excel
in the following example you get output '0' for a strange reason. Can someone explain why this occurs and what I can do about it? (The format of the cells is "text")
rows A:A
---------
1
11
111
22
2
1111
formula:
--------
=COUNTIF(A$1:A$65536;"1*")
result:
-------
0
wanted result:
--------------
4
To add leading zeros, use the TEXT function:
=TEXT(A1,"0000")
To append text, use &
= C1&B1
In B1 enter:
=LARGE($A$1:$A$9,1)
In B2 enter the Array Formula:
=MAX(IF(A$1:A$9<B1,A$1:A$9))
and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.
Basically, we exclude previously found items from the Large() / Max() range.
Best Answer
I think your values are numbers. Test by using
=ISNUMBER(A1)If you apply text format after entry it doesn't change the format to text - you either have to apply text format before entry or enter the values with a preceding apostrophe (to indicate text) like
'111COUNTIFfunction with wildcards doesn't work with numbers. You can use this formula to get the required result (which will work with text, numbers or a mix)=SUMPRODUCT(ISNUMBER(SEARCH(1;A$1:A$65536))+0)