+ Reply to Thread
Results 1 to 7 of 7

copying a countif formula

  1. #1
    Registered User
    Join Date
    10-08-2019
    Location
    Cincinnati Oh
    MS-Off Ver
    2016
    Posts
    8

    copying a countif formula

    I am in need of help, and hoping there is an easy fix for what i want to do.

    In my workbook i have sheet "Activities"
    Column A is the provider
    Column C is the type of activity
    Column E is the quarter the activity took place in.

    I also have a sheet for source data. The data entered in the Activities sheet is validated by the data on the Source sheet.
    So on the source sheet, i have a list of providers and a list of activities.

    I have another sheet for quarter 1. In this sheet i have the function: =COUNTIFS('1920 Teaching activity details'!A:A, 'source data'!A2, '1920 Teaching activity details'!C:C, 'source data'!C9,'1920 Teaching activity details'!E:E, "Q1"). Basically, i want to know how many times provider 2 did activity 9 in quarter 1 of 2019. The function above works perfectly for that. However, i have to have this formula copied to dozens of providers. I tried copying and pasting. When i paste, the source data A2 pastes sequentially down the line which works fine, because the next line would be for provider in source A3, the next one for the provider in source 4, etc. The problem i have is the formula is also going sequential for the activity. So it says C10 in the next line, and then C11. I want to keep all the criteria the same with the exception of the provider. Is there an easy way to accomplish this so that i dont have to type this forumla thousands of times?

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: copying a countif formula

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

    4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Registered User
    Join Date
    10-08-2019
    Location
    Cincinnati Oh
    MS-Off Ver
    2016
    Posts
    8

    Re: copying a countif formula

    Here is a mock-up (the paste didnt capture the outlines of the cells.)

    source data:

    Provider Type of teaching Qtr
    Smith lecture Q1
    Jones board review Q2
    Miller student mentor Q3
    Thomas symposium Q4
    Williams presentation
    Davis
    Daniels
    Martin

    Activity sheet:

    Provider Type of Teaching Date QTR
    Miller lecture 1/1/2000 Q1
    Jones presentation 1/1/2000 Q1
    Martin symposium 1/1/2000 Q3
    Miller student mentor 1/1/2000 Q4
    Davis board review 1/1/2000 Q2
    Williams lecture 1/1/2000 Q1
    Daniels lecture 1/1/2000 Q1

    Q1 sheet:
    Provider lecture symposium board review student mentor presentation
    Smith 0 0 0 0 0
    Jones 0 0 0 0 1
    Miller 1 0 0 1 0
    Thomas 0 0 0 0 0
    Williams 1 0 0 0 0
    Davis 0 0 1 0 0
    Daniels
    Martin

    I do the countif to see how many times Smith did a lecture in Q1. I am trying to copy that countif formula for each of the providers below him, but it doesnt stick with the teaching activity criteria, it moves it down to the next one, "board review" etc. I will have this formula for hundreds of providers, and will do it in each column for that said teaching activity.

  4. #4
    Registered User
    Join Date
    10-08-2019
    Location
    Cincinnati Oh
    MS-Off Ver
    2016
    Posts
    8

    Re: copying a countif formula

    the lines are all wonky because it wouldnt paste the cells properly. I hope you can make it out.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: copying a countif formula

    That is not an Excel sheet. Please attach an Excel sheet.

  6. #6
    Registered User
    Join Date
    10-08-2019
    Location
    Cincinnati Oh
    MS-Off Ver
    2016
    Posts
    8

    Re: copying a countif formula

    I'm trying. Is it attached now?
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,550

    Re: copying a countif formula

    Hello feldkatc and Welcome to Excel Forum.
    You don't need the references to the source data sheet as the providers are in column A and the types of teaching are in row 1.
    This formula will provide the count:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Select cell B2 and paste the formula into the formula bar, then drag the fill handle over to cell M2. While cells B2:M2 are still selected drag the fill handle down to cell M50.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Copying an array formula down keeps copying data in top row
    By blackrosepetals9169 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-10-2018, 05:15 AM
  2. 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
  3. Replies: 8
    Last Post: 05-27-2017, 07:04 AM
  4. [SOLVED] Copying formula result to new cell, without copying formula itself
    By nappyjim1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2016, 02:24 AM
  5. Copying COUNTIF across Columns but range changing incrementally
    By Staniel1975 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-02-2016, 10:54 PM
  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. Copying COUNTIF function down column increases each row by 1 (as a series)
    By Moonbather in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2013, 07:05 PM

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