# Subtotal(offset) function results error

##### New Member
I have got the formula combination in this group, and it really a good one. but I am suffering a bit to understand why its acting weird when I starting the column function in a distance and giving the wrong subtotal value?

#### Attachments

• 30.9 KB Views: 8

#### bosco_yip

##### Excel Ninja

Change your L6 formula, Column function :

From >>

COLUMN(\$D\$5:\$J\$5)

Into >>

COLUMN(\$D\$5:\$J\$5)-COLUMN(\$D\$5)+1

So,

it will always start from 1, and become --> {1,2,3,4,5,6,7}

Regards

Last edited:

##### New Member
Change your L6 formula, Column function :

From >>

COLUMN(\$D\$5:\$J\$5)

Into >>

COLUMN(\$D\$5:\$J\$5)-COLUMN(\$D\$5)+1

So,

it always start from 1 and become --> {1,2,3,4,5,6,7}

Regards
thank you for your explanation, the concept is clear to me now.

##### New Member
@bosco_yip, can you help me to get the cell number and value of the last cell that meets the criteria as well?

Last edited by a moderator:

#### bosco_yip

##### Excel Ninja
@bosco_yip, can you help me to get the cell number and value of the last cell that meets the criteria as well?