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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Copying COUNTIF across Columns but range changing incrementally

LinkBack

Thread Tools

Rate This Thread

Display

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

Countif with a changing range

[SOLVED] copying changing range

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions