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

Trying to count areas that are within a specified range

J

JLAlt

New Member
I have a table of locations and want to count the amount of locations that are within a specified range. I can't get the formula to count correctly.
 

Attachments

  • Book.xlsx
    10.6 KB · Views: 5
I tried 2 formulae, one for overlaps range and the other lies within it.
Code: 
Enclosed
= COUNTIFS(
  Location[Start], ">="&[@Start],
  Location[End],   "<="&[@End])
      
Overlap
= COUNTIFS(
  Location[Start], "<"&[@End],
  Location[End],   ">"&[@Start])
 

Attachments

  • Overlap.xlsx
    15.6 KB · Views: 2
Note: Inside a table, the formula fills down. Using Excel 365 outside the table gives all the results as a dynamic array if the @ is removed.
 

Attachments

  • Overlap.xlsx
    16.6 KB · Views: 3
You must log in or register to reply here.
Back
Top