Results 1 to 12 of 12
Thread: Count Formula (2002)

20050707, 14:05 #1
 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

20050707, 14:08 #2
 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

20050707, 14:10 #3
 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

20050707, 14:13 #4
 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

20050707, 15:59 #5
 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

20050707, 16:14 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20050707, 16:56 #7
 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

20050707, 17:10 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20050707, 17:12 #9
 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

20050707, 17:14 #10
 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

20050707, 17:27 #11
 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

20050707, 17:29 #12
 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