Count various cell values

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
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

ADVERTISEMENT

For case sensitive
=SUMPRODUCT(--(EXACT(C1:C100,{"LEDGER"})))
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
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

ADVERTISEMENT

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
959
Office Version
  1. 2007
Platform
  1. Windows
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
959
Office Version
  1. 2007
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top