Excel, how to count every 4th cell in column to see if occupied?

[Guest]

hello, i want to use a COUNTA function to count the occupied cells in each
column in an excel spreadsheet. However, the cells i want to count are every
4th cell & not the ones inbetween. i can use =COUNTA(c7,c11,c15,c19....) but
my list size goes into to 1,000's & i only know how to enter the above
formula mannually. I need to know how to write the function/formula to count
every 4th cell automatically. i would be glad for anyone's help.

thank you!

 

[Guest]

I would use a helper column to identiry each row you wish to count, then use
AutoFilter to filter for them in that column and for non-blanks in the data
column, and use =SUBTOTAL(3,a:a)

hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

alternatively:

=SUMPRODUCT(--(MOD(ROW(A1:A10),4)=0),--(A1:A10<>""))

 

[Maistrye]

hello, i want to use a COUNTA function to count the occupied cells in
each
column in an excel spreadsheet. However, the cells i want to count are
every
4th cell & not the ones inbetween. i can use
=COUNTA(c7,c11,c15,c19....) but
my list size goes into to 1,000's & i only know how to enter the above
formula mannually. I need to know how to write the function/formula to
count
every 4th cell automatically. i would be glad for anyone's help.

thank you!

Try this:

=SUM(COUNTA(INDIRECT(ADDRESS(4*ROW(1:1000)+3,COL(C7)))))

(Make sure to enter it with CTRL-SHIFT-ENTER)

Change the 1000 to the number of rows you want to look at, and you can
stick the actual number in for COL(C7) if you want. This formula will
start
at the 7th row and check every 4th from there.

HTH

Scott

 

[Guest]

Hi CLR, i am not sure what you mean. Here's an example of waht i have:

a b
1 lee smith 27-July
2 123 street Resch
3 sea, oh 44759 1-Aug
4 000-000-0000
5 tim elliot 10-June
6 258 avenue
7 tall, va 68921
8 222-222-2222

At the bottom of many names and addresses - i only want to cout the date
next to the name as one - the other cells (b2-b4) may be occupied with data
but i don't want to count them i only want to count every 4th cell in column
b (which is b1,b5...so on)
Thanks for your help!!