+ Reply to Thread
Results 1 to 5 of 5

COUNTIF for every nth row

  1. 10-10-2021, 11:01 AM #1
    luke43
    luke43 is offline
    Registered User
    Join Date
    08-15-2018
    Location
    UK
    MS-Off Ver
    10
    Posts
    7

    COUNTIF for every nth row

    I have multiple patients with 135 rows of data each in sheet 1. What I want to do is count the number of cells across a set number of columns with values > 0. Starting with C30 in sheet 1 and across a set number of columns (value in sheet 2 A2), for every 135th row in sheet 1, and place the result in sheet 2 B2. Then if I copy down the formula in the sheet 2 B column, the formula will repeat for the (30+135)th row (starting from C(30+135)) in sheet 1 across (sheet 2 A3) columns, etc. Hope this makes sense, can anybody help? Many thanks.
    Reply With QuoteRegister To Reply
  2. 10-10-2021, 11:10 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,812

    Re: COUNTIF for every nth row

    There are instructions at the top of the page explaining how to attach your sample workbook.

    Administrative Note

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. 10 is Windows, NOT your Office version. Thanks.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 10-10-2021, 11:21 AM #3
    bsalv
    bsalv is offline
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: COUNTIF for every nth row 

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
    Reply With QuoteRegister To Reply
  4. 10-10-2021, 02:24 PM #4
    luke43
    luke43 is offline
    Registered User
    Join Date
    08-15-2018
    Location
    UK
    MS-Off Ver
    10
    Posts
    7

    Re: COUNTIF for every nth row

    Many thanks but the formula didnt work for my workbook so I have attached a sample workbook. What I want to do is have a formula in sheet 2 C1 which counts
    the number of cells with a value >0 across a range of columns starting from sheet 1 C30 and then for every 135th row after this. The range of columns is
    indicated in sheet 2 B.

    So the first answer in sheet 2 C1 is 9 (10 columns across C30 in sheet 1, as indicated by sheet 2 B1 (10), with 9 out of 10 cells having a value >0). THe second
    answer in sheet 2 C2 would be 3 (5 columns across C165 (30+135) in sheet 1, as indicated by 5 in sheet 2 B2, with 3 out of 5 cells having a value >0). Hope this
    makes sense. Many thanks for any help you can give me.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 10-12-2021, 10:31 PM #5
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,557

    Re: COUNTIF for every nth row

    This proposal involves row numbers to be counted being placed in column A on Sheet2.
    Cell A1 is populated with the starting row number, 30.
    Cell A2 is populated using: =SUM(A1,135)
    Cells C1:C2 are populated using: =COUNTIFS(INDEX(Sheet1!C$1:C$165,A1):INDEX(INDEX(Sheet1!C$1:L$165,,B1),A1),">0")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Countif on visible cells only & Countif from a cell with multiple entry
    By MushroomJ in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-26-2021, 05:37 AM
  2. [SOLVED] Help with countif formula for calendar (countif any date in table present)
    By KMJ256388 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2021, 12:11 PM
  3. IF statements with COUNTIF (including results of countif w/o having it in a separate cell
    By olga6542 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-12-2020, 05:33 PM
  4. Adding a COUNTIF function to an existing IF AND COUNTIF Formula...
    By Ourkid123uk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2018, 10:09 AM
  5. How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it further?
    By Dussy in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-27-2017, 07:04 AM
  6. Faster if/countif formula for flagging uniques - countif too slow
    By Speshul in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2015, 01:44 PM
  7. Combining a Date Range COUNTIF and a general COUNTIF
    By jacobtom in forum Excel General
    Replies: 1
    Last Post: 09-15-2011, 05:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1