short way of counting number of days in 2 ranges that finish inside a third Hi a

Question

short way of counting number of days in 2 ranges that finish inside a third

Hi all,

I have a year range say 1-4-2013 to 1-4-2014 and i have 2 conditions, vacant and in active, the sample can be one, both or none. is there a way with a function to count the number of say that one or both conditions are met. an example would be condition 1 1-9-2013 to 5-9-2013 with condition 2 being 01-09-2013 to 15-09-2013, I would need the answer to show 15 raher than 19... been going round in circles trying to solve this one, only way i can see is to create 3 columns with all of the days in the year range in the first a check in col 2 and three to see if condition 1 or 2 is met and then a fourth column to check if either condition is met then count the number of 4th positives.  Not ideal as i have lots of samples to process, likewise if i went the macro route.

Thanks !!

Explanation / Answer

Hi..

Hi,

The link is not valid.

Based on Herbert's approach.

Change Table2 to:

The formula for count:

=SUMPRODUCT((INDIRECT("Table"&F6&"[Date]")>=[@Start])*(INDIRECT("Table"&F6&"[Date]")<=[@End])*NOT((INDIRECT("Table"&F6&"[Vacant]")=0)*(INDIRECT("Table"&F6&"[Active]")=0)))

INDIRECT("Table"&F6&"[Date]") creates an "indirect"' way to refer to Table1[Date]

You can now just change the table nr to refer to another table.

Table Nr Start End Count CountB 1 09/01/13 09/05/13 4 4 1 09/01/13 09/15/13 12 12 Total 16 16

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.