CODEWITHSUNDEEP
excel
Camberra CacaitA
Camberra CacaitA

Reputation: 21

Changing countif range automatically

I'm using this formula to count the word red in the last 30 days but the range is not changing when a new row is added at the bottom.

How can I change the formula so when a new row is added at the bottom the range is adjusted automatically? The quantity of rows to check is always 30.

=COUNTIF(D2433:D2463, "Red")

Upvotes: 0

Views: 2898

Answers (4)

yass
yass

Reputation: 869

Use the following: 

=COUNTIF(OFFSET($D$2433,0,0,30,1),"Red")

The references you are using $D$2433 till $D$2463 count 31 if you need 31 change in the above formula 30 to 31
Offset will start in $D$2433 and count the rows down in the same column
Countif will count if Red

Update
If in column D you insert only Data to be counted, you can use : 

=COUNTIF(OFFSET(INDIRECT("$D$"&MAX(IF($D:$D<>"",ROW($D:$D),0))),0,0,-30,1),"Red")

This is an array formula press Ctrl+Shift+Enter instead of just Enter
The new formula will find the last non empty row in column D and Indirect will correct the formula automatically

Upvotes: 0

Forward Ed
Forward Ed

Reputation: 9874

=COUNTIF(INDEX(D:D,AGGREGATE(14,6,ROW(D:D)/--((D:D)<>""),1)-30):INDEX(D:D,AGGREGATE(14,6,ROW(D:D)/--((D:D)<>""),1)),"Red")

Conditions:

There all rows beneath the last row must be blank There cannot be any blank row within the last 30 row or your count may be off.

This is an array like function. If you use full range references it checks 1048576 cells each time the aggregate function references the full column range. As such if you use the formula a lot you can bog down your computer, if you are only using it a couple of time you will probably be ok.

Upvotes: 0

ian0411
ian0411

Reputation: 4265

Another technique is using a dynamic named range:

  1. Formula > Name Manager > New: Give a Name first and then insert the following formula into Referes to field. Also see the picture.

=INDEX(Sheet1!A:A,1):INDEX(Sheet1!A:A,INDEX(MAX((Sheet1!$A:$A<>"")*(ROW(Sheet1!A:A))),0))

This should give you a range like $A$1:$A$300 depends where the last non-empty cell is.

  1. Now in the cell that you want to evaluate, add this formula:

=COUNTIF(rng,"red")

Please note rng is the named range that you should change to the name you want to use.

This will find the last cell and even work for blank cells. Try and let me know if you have any question.

enter image description here

Upvotes: 1

JensS
JensS

Reputation: 1151

You need a relative cell reference. You can do this with the

=OFFSET()

function.

Upvotes: 0

Related Questions