Results 1 to 3 of 3

Thread: [RESOLVED] Analyzing an Excel worksheet.

  1. #1

    Thread Starter
    PowerPoster eranga262154's Avatar
    Join Date
    Jun 2006
    Posts
    2,201

    Resolved [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.
    “victory breeds hatred, the defeated live in pain; happily the peaceful live giving up victory and defeat” - Gautama Buddha

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Analyzing an Excel worksheet.

    vb Code:
    1. Sub CountOccurances()
    2.     '~~> Depending upon your conditions change this
    3.     Dim Ar(7) As String
    4.     Ar(0) = "<100"
    5.     Ar(1) = "<200"
    6.     Ar(2) = "<300"
    7.     Ar(3) = "<500"
    8.     Ar(4) = "<700"
    9.     Ar(5) = "<1000"
    10.     Ar(6) = "<1500"
    11.     Ar(7) = "<2000"
    12.    
    13.     '~~> Storing the formula, starting from C1
    14.     For i = 0 To UBound(Ar)
    15.         Sheets("Sheet1").Range("C" & i + 1).Value = _
    16.         "=COUNTIF(B:B, " & """" & Ar(i) & """" & ")"
    17.     Next
    18. End Sub
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    PowerPoster eranga262154's Avatar
    Join Date
    Jun 2006
    Posts
    2,201

    Re: Analyzing an Excel worksheet.

    Thanks, actually I've solve the question with macros. Thanks for the comment lol.
    “victory breeds hatred, the defeated live in pain; happily the peaceful live giving up victory and defeat” - Gautama Buddha

Posting Permissions

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



Click Here to Expand Forum to Full Width