# Thread: Count Formula (2002)

1. ## 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. ## 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)

3. ## 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. ## Re: Count Formula (2002)

Try:

=COUNTIF(A1:A1000,"StoreA")

5. ## 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. ## 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. ## 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. ## 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. ## 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?

10. ## 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. ## 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>

12. ## 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
•