Count various cell values

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
In column C, I generate various values through C%:C9999. Lot of generated values are EXACT.
How to COUNT the number of various generated values in column C.
"Ledger" & "LEDGER" should be counted separately.
Similarly, "Led ger" & "Ledger" should be counted separately.
Similarly, " Ledger" & "Ledger" should be counted separately.
If a blank is generated in column C, it should be counted separately.
If a "#N/A" is generated in column C, it should be counted separately.
If a "#Ref!" is generated in column C, it should be counted separately.

Thanks in advance.
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
Request you to please provide the formula as I do not case-sensitive matching at all.
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
For case sensitive
=SUMPRODUCT(--(EXACT(C1:C100,{"LEDGER"})))
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
but apart from LEDGER, lot of values are generated in the column. how to count each generated values?
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
For any text: "LEDGER"," led ger"....
=SUM(--(IF(ISERROR($C$1:$C$10),0,EXACT($C$1:$C$10,{"LEDGER"}))))

for #N/A
=COUNT(1/(IF(ISNA($C$1:$C$10),1,$C$1:$C$10)))

For #REF:
Calculate it as the last one with
=COUNTA(C1:C10)-SUM(F1:F4)

For blank:
=COUNTBLANK(C1:C10)

CDEF
Ledger
1
LEDGER
1
Led ger
1
#REF!
#NA
2
#N/A
#REF
2
#N/A
3
#REF!
Ledger
LEDGER
Led ger

<TBODY>
</TBODY>
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
Should I write formulas in column F SEPARATELY for '4 varieties': any text; #N/A, #REF! or blank?
How to generate column E? Moreover preferably in ascending order?
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Yes each formula for one entr.
E is manull typing.

First and second formula shoudl be confirm with Control+Shift+Enter
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
Yes each formula for one entr.
E is manull typing.

First and second formula shoudl be confirm with Control+Shift+Enter
Please reply below 2 points
First and second formula means for "any text" and "#N/A"?
For third & fourth formulas, no need of Control+Shift+Enter?
 

Forum statistics

Threads
1,085,075
Messages
5,381,558
Members
401,744
Latest member
Schoen

Some videos you may like

This Week's Hot Topics

Top