copying a countif formula

**feldkatc** · 10-08-2019, 12:37 PM

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?

**Glenn Kennedy** · 10-08-2019, 12:42 PM

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.

**feldkatc** · 10-08-2019, 01:13 PM

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.

**feldkatc** · 10-08-2019, 01:15 PM

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

**Glenn Kennedy** · 10-08-2019, 01:18 PM

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

**feldkatc** · 10-08-2019, 01:22 PM

I'm trying. Is it attached now?

**JeteMc** · 10-10-2019, 09:06 PM

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:

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.

copying a countif formula

LinkBack

Thread Tools

Rate This Thread

Display

copying a countif formula

Re: copying a countif formula

Re: copying a countif formula

Re: copying a countif formula

Re: copying a countif formula

Re: copying a countif formula

Re: copying a countif formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copying an array formula down keeps copying data in top row

Adding a COUNTIF function to an existing IF AND COUNTIF Formula...

How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it further?

[SOLVED] Copying formula result to new cell, without copying formula itself

Copying COUNTIF across Columns but range changing incrementally

Faster if/countif formula for flagging uniques - countif too slow

Copying COUNTIF function down column increases each row by 1 (as a series)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions