# Query on Row count basis matching values

#### skarnik01

##### Member
Hello guyz,

I have a query with regards to getting a formula / function to calculate count of rows upto last matching date and also count of rows to next last row of matching dates

Attaching an excel with some coloring for easy understanding.

In the attached example, on Row 2, Date2 has date as 6-Sep-18. So I require to have a formula / function in cell C2 which calculates value as 3 to reach upto last row of Date2 with value as 6-Sep-18.
Similarly, D2 should calculate value as 7 basis the number of rows count required to reach last row of Date2 with value as 12-Sep-18.....and so on.

I have more than 2000 rows in my excel and thereby it will be difficult to perform this task manually.

Thank you in advance and Happy New Year to all

#### Attachments

• 11.1 KB Views: 9

#### bosco_yip

##### Excel Ninja
Maybe..........

1] In C2, formula copied down :

=NETWORKDAYS(A2,E2)-1

2] In D2, formula copied down :

=COUNTIF(\$E\$2:\$E\$60,E2)+NETWORKDAYS(A2,E2)-1

Regards

#### skarnik01

##### Member
Thank you for your response. I tried Workdays before and it gives the discrepancy as marked in attached file.
For D2, is there a way to use Countif along with Offset and maybe get the count of Date2 values against the row in which the formula persists.

Thanks again

#### Attachments

• 14.7 KB Views: 2

#### bosco_yip

##### Excel Ninja
In respect to your 2nd attachment as per post #.3, which does not show what did you wanted ?

Without in understand your expected results and requirement, I'm afraid unable to give you further help.

Regards

#### Peter Bartholomew

##### Well-Known Member
I am not sure whether you wish to return the difference in record numbers, working days or even days. If you wish to identify data by record number, it would be good to add a field containing the record numbers. If you want working days then that is fine but, since you have missing dates, this will not correspond to row numbers.

n.b. the attached workbook relies upon dynamic array functionality (the XLOOKUP function in particular) that is not yet available in production releases of Excel.

#### Attachments

• 22.5 KB Views: 3