Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Count Formula (2002)

    Hi,

    I need to obtain the count of items in a column has names of stores. I need to obtain the count of the each name in the column. I can do this with a pivot table but need to do it in code. What is the formula that I would put into the cell?

    Thanks,
    Leesha

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Formula (2002)

    Are you trying to count the number of cells that are not empty? If so, then the following will count the number of non empty cells in the range A1:A1000

    =COUNTA(A1:A1000)
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Formula (2002)

    Hi,

    No I'm trying to count the number of stores with by a specific name. There are a finite number of different names in the column but the count may vary from day to day. IE if there is storeA StoreB and StoreC, I need the count of StoreA.

    Leesha

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Formula (2002)

    Try:

    =COUNTIF(A1:A1000,"StoreA")
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Formula (2002)

    PERFECT! Thank you!

    Now if I could ask for one more formula. I also need to get the dollar sum for the stores so the user see's the count of the store (which I now have) and the total sales. The sales are in column H and the stores are in column E. I tried a few things but the syntax isn't right. If I can "see" it I can then understand it.

    Thanks,
    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Formula (2002)

    Does this work for you?

    <code>=SUMIF(E1:E1000,"StoreA",H1:H1000)</code>

    The first argument is the range to test on, the middle argument is the condition, the last argument is the range to calculate the sum on.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Formula (2002)

    Thanks Hans! That worked great.

    The spreadsheet that this I'm working on is an export from Access so the data changes repeatedly. I've got it set to format the way user wants via a macro in their personal workbook. Since the data / number of rows is always changing (columns stay the same), I've been using activecell.offset............ to define what needs to be formatted. This has worked well till these formulas. Is it even possible to do the formula so that the highlighted cells are used as the reference vs. the actual cell range? If not or if its too labor intensive the user can manually figure the data. 95% of the other stuff they will need will be taken care of for them. I just figured I'd check.

    As always thanks for the explanations! These formulas will come in very handy in other work!

    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Formula (2002)

    To let a formula refer to the current selection would take a custom VBA function, but that may not be what you intended.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Formula (2002)

    ActiveCell.Offset only works in VBA, not in formulas. I'm not sure what the problem is here. The formulas that Hans and I gave you should work fine if you specify a range that is the largest that the data could ever occupy. Does that solve your problem?
    Legare Coleman

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Formula (2002)

    The formulas are perfect. To be honest, I didn't even think the have the do the largest range possible but was limiting them to the cells that had data in them. That is a wonderful idea!!!

    Thanks Guys,
    Leesha

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Formula (2002)

    Sometimes you have to think outside the data range. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Legare Coleman

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Formula (2002)

    How true!!! To funny!

    Have a great day,

    Leesha

Posting Permissions

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