• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • Query1.xlsx
    11.1 KB · Views: 9
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

  • Query1.xlsx
    14.7 KB · Views: 2
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
 
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

  • temp.xlsx
    22.5 KB · Views: 3
Back
Top