counting the number of cells at intervals of a string in column C

**Astro** · Apr 13th, 2010, 08:54 AM

Hi I would like to count the number of cells in my data.

The cell intervals are seperated by this string "SICOM" in column C.

Can someone help me?

Thank you very much.

**Siddharth Rout** · Apr 13th, 2010, 09:33 AM

Can you give an example?

**westconn1** · Apr 14th, 2010, 07:10 AM

vb Code:

cnt = Application.WorksheetFunction.CountIf("c:c", "SICOM")

**Astro** · Apr 14th, 2010, 08:03 AM

Thanks I'll try that out.

Here's an example..

I'm trying to calculate the number of cells from the first 'XICOOMM' to the next 'XICOOMM'.

I wanna fill up the first 2 column with primary keys for referencing.

and then the XICOOMM at ie, row 50.. to the very next XICOOMM.

the data generated only states the primary key once, somewhere above the data in column say 'E'.

Thank you!

**Siddharth Rout** · Apr 14th, 2010, 08:42 AM

Lets say the data is in Cell A1 to A15

Non VBA Method

=CountA(A1:A15)

The above formula will give you the number of cells which has alphanumeric values in the cells. If you want to exclude the two cells which have XICOOMM then the above formula becomes

=CountA(A1:A15) - 2

VBA Method

Application.WorksheetFunction.CountA("A1:A15") will give you the same results as above. Subtracting two will be required if you want to exclude the cells which contain XICOOMM.

Hope this is what you wanted?

**Astro** · Apr 14th, 2010, 09:19 AM

hey that is close, however i need the formula to read the 'XICOOMM' text..

so from 1st XICOOMM to next (2nd XICOOMM), perform a count

from 2nd XICOOMM to the next (3rd XICOOMM; count.

Thank you

**Siddharth Rout** · Apr 14th, 2010, 10:20 AM

Do you also want to count the empty cells between cells which have XICOOMM?

**Astro** · Apr 14th, 2010, 11:12 AM

yea no harm in that! =)

i just need to prepare the data such that it has a primary key.

the primary key is only stated once in column d~

gotta have a formula to fill in column A or B

**Siddharth Rout** · Apr 14th, 2010, 11:21 AM

One last question. Do you want to use VBA or Non VBA Method?

**Astro** · Apr 15th, 2010, 01:49 AM

i would like to explore both~

**westconn1** · Apr 15th, 2010, 05:31 AM

looks like you need to use excels find method to find xicooom, then findnext to find the next occurance, calculate the rows between

**anhn** · Apr 15th, 2010, 07:30 AM

Perhaps this does not help much:

Thread: counting the number of cells at intervals of a string in column C

Thread Tools

Display

counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Re: counting the number of cells at intervals of a string in column C

Posting Permissions