CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
mani_nz
mani_nz

Reputation: 5592

Calculate the COUNTIFS criteria range dynamically

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

Upvotes: 1

Views: 8008

Answers (4)

Spencer Ogden
Spencer Ogden

Reputation: 306

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.

Upvotes: 1

Forward Ed
Forward Ed

Reputation: 9874

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)

Upvotes: 1

Mrig
Mrig

Reputation: 11702

Try this:

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

Upvotes: 0

Carrosive
Carrosive

Reputation: 899

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...)

Upvotes: 2

Related Questions