# Thread: [RESOLVED] Analyzing an Excel worksheet.

1. ## [RESOLVED] Analyzing an Excel worksheet.

Hi all,

I've an excel sheet of 3 columns. I want to collect some information like follows. Basically count occurrence of a range.

=COUNTIF(B:B, "<100")
=COUNTIF(B:B, "<200")
=COUNTIF(B:B, "<300")
=COUNTIF(B:B, "<500")
=COUNTIF(B:B, "<700")
=COUNTIF(B:B, "<1000")
=COUNTIF(B:B, "<1500")
=COUNTIF(B:B, "<2000")
something like that. May be you are not clear, on column B I have some values. I want to calculate each count as above and put them in cells.

Can anyone give me a solution. Doing it manually is a real mess.  Reply With Quote

2. ## Re: Analyzing an Excel worksheet.

vb Code:
```Sub CountOccurances()
'~~> Depending upon your conditions change this
Dim Ar(7) As String
Ar(0) = "<100"
Ar(1) = "<200"
Ar(2) = "<300"
Ar(3) = "<500"
Ar(4) = "<700"
Ar(5) = "<1000"
Ar(6) = "<1500"
Ar(7) = "<2000"

'~~> Storing the formula, starting from C1
For i = 0 To UBound(Ar)
Sheets("Sheet1").Range("C" & i + 1).Value = _
"=COUNTIF(B:B, " & """" & Ar(i) & """" & ")"
Next
End Sub```  Reply With Quote

3. ## Re: Analyzing an Excel worksheet.

Thanks, actually I've solve the question with macros. Thanks for the comment lol.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•