Dynamic array in countif formula

[GingerNinja]

Hi to all the awesome Excellers!

I have a problem i cant seem to get around, here goes;

I'm using a countif formula for a specific range of cells, but i realise as the months go by, i need the formula to have a dynamic range that will change if and when needed to. I'm using random dates, numbers and cells in my explanation.


Range C2:Q2 has dates in them, they change from time to time, for this eg 1 Jan to 15 Jan


The current formula looks similar to this countif(C3:L3,"A") and i drag it down a column


I'm wanting that if eg, 1 Jan is in cell C2 but then next time changes to D2 as well as 10 Jan being in L2, how would i type a countif formula that would change the range in the formula automatically from C3:L3 to D3:M3? But it must refer to the dates 1 Jan and 10 Jan.


And i need it to be able to be dragged down a column.


I'm using Excel 2010 and the end users also only have Excel 2010, so no need for compatibility checks. I'll put up a sample workbook if this was too vague.


The workbook is a daily production log that logs the products made per person per day. I have allocated letters to the products that are made, hence the use of countif formulae. Different products are made on different days so I'll be using A or B or C etc in the formula, but lol, i dont need help with that And the full sheet has the dates from 1 Jan through to 31 Dec, but i'll be using specific ranges of 10 days each range, its just that the dates can change in the cells due to other controlling formulae.

 

[Sajan]

Hello @GingerNinja,

I am not sure I understood what you are attempting to do. Normally, Excel would automatically adjust the formulas when you insert columns, move the data range, etc. Are you changing the data range through some other way?


In your example, you refer to dates, but then use "A" in your countif formula. Are you trying to count the cells that match a given product and fall in the date range Jan 1 to Jan 10 (in your second example)?


How does the date range column shift to the right? Does that happen when you insert new columns, or due to some other event? Do the start dates always remain the same? Do the end dates grow over time? Are these dates the last data values in a row?


To make your problem definition clear, please provide some sample data, or consider uploading a workbook.


Cheers,

Sajan.

 

[Montrey]

Ginger,


Create a dynamic named range!

Press Ctrl+F3 to get to the named range manager.

Then paste the following in the formula box

=OFFSET(Sheet1!$B$3,,MATCH(Sheet1!$A$1,Sheet1!$C$3:$L$3,0),1,MATCH(Sheet1!$A$2,Sheet1!$C$3:$L$3,0)-MATCH(Sheet1!$A$1,Sheet1!$C$3:$L$3,0)+1)


Make sure you name it something. ex. myrange


Then substitute myrange for c3:L3 in your countif formula.


Enjoy!


(make sure you change the above formula to the correct sheet name instead of 'sheet1')

 

[Debraj]

Requesting..

I'll put up a sample workbook if this was too vague.