+ Reply to Thread
Results 1 to 9 of 9

Countif Formula copied down where the range changes based on subgrouping

  1. #1
    Forum Contributor
    Join Date
    08-10-2015
    Location
    Sarasota, FL
    MS-Off Ver
    MS 365
    Posts
    77

    Countif Formula copied down where the range changes based on subgrouping

    I have data that is pulled every week and subgrouped based on Count, and I have to do the following function in multiple columns in the new spreadsheet every week.....

    =COUNTIF(G2:G8,">=0:16")

    I need to copy this down the entire column (upto 1500 rows) every week, where the rows are subgrouped based on the client name in column A

    In the attached file:
    column H shows the result that I'm looking for from G - using the formula above
    I really don't want to have to change the range over 100 times every week.

    Ideas? I tried using a different formula that I am using for something else....
    =IFERROR(SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(" Total",A2)),IF(SUBSTITUTE(A2," Total","")=$A$2:$A$64,INT($E$2:$E$64),""),""),IF(ISNUMBER(SEARCH(" Total",A2)),IF(SUBSTITUTE(A2," Total","")=$A$2:$A$64,INT($E$2:$E$64),""),"")),1,0)),"")

    but couldn't figure out where the countif formula goes.....
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: Countif Formula copied down where the range changes based on subgrouping

    Just use COUNTIFS. Put the following formula in cell H9:

    =COUNTIFS(G:G,">=0:16",A:A,A8)

    and copy into the other cells where you want the formula.
    Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.

    Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
    If you're happy with an answer given, please click the * under the person's name to boost their reputation.

  3. #3
    Forum Contributor
    Join Date
    08-10-2015
    Location
    Sarasota, FL
    MS-Off Ver
    MS 365
    Posts
    77

    Re: Countif Formula copied down where the range changes based on subgrouping

    Craig,
    My excel tells me there are too many arguments - it doesn't like the A:A (actually the second A list there). Carol

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,173

    Re: Countif Formula copied down where the range changes based on subgrouping

    Try this in H2 and copy down

    =IF(ISNUMBER(SEARCH("total",A2,1)),COUNTIFS(G:G,">=0:16",A:A,A1),"")

  5. #5
    Forum Contributor
    Join Date
    08-10-2015
    Location
    Sarasota, FL
    MS-Off Ver
    MS 365
    Posts
    77

    Re: Countif Formula copied down where the range changes based on subgrouping

    That works John! Thanks!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,173

    Re: Countif Formula copied down where the range changes based on subgrouping

    Thanks in part to Craig: I added to his solution.

  7. #7
    Forum Contributor
    Join Date
    08-10-2015
    Location
    Sarasota, FL
    MS-Off Ver
    MS 365
    Posts
    77

    Re: Countif Formula copied down where the range changes based on subgrouping

    Thanks John - I added Reps to both of you

  8. #8
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: Countif Formula copied down where the range changes based on subgrouping

    I'm glad you got to an answer you can use. But I have to ask, when you initially used the formula I provided, did you use =COUNTIFS() or just =COUNTIF()? The point of countifs is to incorporate multiple criteria.

  9. #9
    Forum Contributor
    Join Date
    08-10-2015
    Location
    Sarasota, FL
    MS-Off Ver
    MS 365
    Posts
    77

    Re: Countif Formula copied down where the range changes based on subgrouping

    Craig - I thought I had copied/pasted and changed the range to what I needed in my actual spreadsheet, but it is possible that I manually typed it and missed the "s" all together. That would make sense, then that it didn't work. Unfortunately, I am multi-tasking with numerous spreadsheets to try and extract all the data I need, and I simply don't recall for certain. I really do appreciate the assistance please do not think for a minute that I don't.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 07-12-2015, 09:45 AM
  2. Countif based on a range
    By mcranda in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-27-2015, 11:03 AM
  3. [SOLVED] Countif based on date range
    By ciresuark in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-26-2014, 07:11 PM
  4. Range based COUNTIF
    By cclayton in forum Excel General
    Replies: 3
    Last Post: 12-16-2010, 05:38 AM
  5. Grouping and Subgrouping
    By megfourfun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2010, 03:47 PM
  6. Naming a Range Code:formula is then copied
    By dgo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-08-2009, 09:13 AM
  7. macro for subgrouping
    By omdkhaleel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-08-2008, 07:01 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1