Calculate the COUNTIFS criteria range dynamically

1
votes

I have a excel formula as below,

COUNTIFS($A$8:$A$14,$A8,$B$8:$B$14,$B8)

Here I want the criteria range to be calculated with a simple logic.

Instead of $A$14 I want this value to be calculated A$8+4 i.e. $A$14

In other words, it should get the current row and add 4 to be the criteria range for COUNTIFS

How can this be done is excel within the COUNTIFS formula?

Cheers

4
excelexcel-formulaexcel-2010
you say get the CURRENT row, but in your example you have locked the row at a value of 8. so if I was in row 32, do you want it to be $A$8:$A$36 as 36 would be 4+32? or do you mean you want it to be 4 added to whatever row is the start of the range? – Forward Ed

4 Answers

2
votes

You could use =OFFSET(Cell Reference, Rows, Columns) within your formula to achieve this, for example:

=COUNTIFS($A$8:OFFSET($A$8,6,0),$A8, etc...)

1
votes

Lets assume that the size of your range to be returned is stored in the cell D1.

=COUNTIFS($A$8:INDEX(A:A,ROW($A$8)+D1),$A8,$B$8:INDEX(B:B,ROW($B$8)+D1),$B8)

If you want the range to be defined as a certain number of rows after the current row as stated in your question, then still assuming the number of rows to be added is in D1, you would use the following:

 =COUNTIFS($A$8:INDEX(A:A,ROW()+D1),$A8,$B$8:INDEX(B:B,ROW()+D1),$B8)
1
votes

Expanding on Oliver's answer. The full format of OFFSET allows you to specify a whole range, not just one cell. OFFSET(Reference, Row Offset, Col Offset, Height, Width) so you could do:

OFFSET(A8,0,0,4,1)

This specifies the range A8:A11. In the COUNTIF

=COUNTIF(OFFSET(A8,0,0,4,1),A8,...)

Locking cells with $ works the same way within OFFSET if needed.

0
votes

Try this:

=COUNTIFS(INDIRECT("$A$8:$A$" & 8+6),$A8,INDIRECT("$B$8:$B$" & 8+6),$B8)