Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel, how to count every 4th cell in column to see if occupied?
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel, how to count every 4th cell in column to see if occupied?
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 "Ashley" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel, how to count every 4th cell in column to see if occupie
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!! "CLR" wrote: 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 "Ashley" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel, how to count every 4th cell in column to see if occupied?
alternatively:
=SUMPRODUCT(--(MOD(ROW(A1:A10),4)=0),--(A1:A10<"")) "Ashley" wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel, how to count every 4th cell in column to see if occupied?
Ashley Wrote: 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(C 7))))) (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 -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=562975 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert date | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |