Copying COUNTIF across Columns but range changing incrementally

**Staniel1975** · 03-02-2016, 06:21 PM

Hi All

I am trying to use the COUNTIF function to return the number of times a name appears in a range of data that is entered by day and total that number by week. I can get that to work as =+(COUNTIF(Sheet1!C$5:I$57,$A4) but i want to report the same info for the next week range of 7 days which is J$5:P$57 in the next column along, when i drag the formula it automatically chooses the range of D$5:J$57. I've tried manually entering two columns to see if Excel learns and drags across accordingly but it doesn't. Any Ideas?

**JeteMc** · 03-02-2016, 07:10 PM

Welcome to Excel Forum.
I think that excel can learn to do this:

=COUNTIFS(INDIRECT(ADDRESS(5,3+7*(COLUMN(A1)-1))&":"&ADDRESS(57,9+7*(COLUMN(A1)-1))),$A4)

Let me know if you have any questions.

**Staniel1975** · 03-02-2016, 08:22 PM

Hi

Thanks for this, i've tried to implement it but i must be making a mistake. can you break it into steps for me so i can understand how to convert it into the formula i need?

Thanks

**JeteMc** · 03-02-2016, 08:43 PM

The formula should be pasted into a cell and then dragged to the right. the +7*(COLUMN(A1)-1) part advances the columns by 7 each time so the range goes from C5:I57 in the cell where the formula is pasted to J5:P57 when dragged to the right one cell.
Here is a file that has the formula applied, perhaps the setup is not matching what was intended in the initial post, however this is what I envisioned:
Range Change Countifs.xlsx
I would suggest running the evaluate formula and see if there is anything that stands out as being dysfunctional.
Let me know if you have any questions.

**FlameRetired** · 03-02-2016, 10:54 PM

Similar to JeteMc's

Formula:

=COUNTIF(INDEX($C$6:$P$57,,(COLUMNS($C:C)-1)*7+1):INDEX($C$6:$P$57,,(COLUMNS($C:C)-1)*7+7),$A$4)

Copying COUNTIF across Columns but range changing incrementally

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copying COUNTIF across Columns but range changing incrementally

Re: Copying COUNTIF across Columns but range changing incrementally

Re: Copying COUNTIF across Columns but range changing incrementally

Re: Copying COUNTIF across Columns but range changing incrementally

Re: Copying COUNTIF across Columns but range changing incrementally

Thread Information

Users Browsing this Thread

Similar Threads

Copying a changing range from another workbook and pasting it after last column

COUNTIF range keeps changing

Checking a range of columns and copying values to appropriate columns based on criteria

[SOLVED] Changing a CountIf Range so it read the end row from a cell

Copying Formulas from a Range of Cells without Changing the Absolute or Relative References in Excel 2007

[SOLVED] Countif with a changing range

copying changing range

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions