Need formula to sum all missing items in concurrent events.

**amdoc** · 03-07-2013, 06:17 PM

See attached file: ConcurrentEventsTotalMissing.xlsx

I added the 'Concurrent Events Total Missing' column data manually because I couldn't figure out the formula to calculate it. Basically I need the total Missing for all the Concurrent Events in that last column. Assistance would be greatly appreciated.

**oeldere** · 03-07-2013, 06:27 PM

See the attached file.

See the red cells (I think you gave manualy the wrong answer).

**amdoc** · 03-07-2013, 06:45 PM

Originally Posted by oeldere

See the attached file.

See the red cells (I think you gave manualy the wrong answer).

Thank you oeldere but that isn't the solution needed. Your formula continues to add missing items to the total just above it. The concurrent events are not in a sequence. When an event closes then it is removed from concurrent events and when an item opens then it is added to concurrent events and that doesn't happen sequentially.

**amdoc** · 03-07-2013, 06:48 PM

Here is the formula that identifies concurrent events:
=SUMPRODUCT((A2>=$A$2:$A$36)*(A2<=$B$2:$B$36))

**oeldere** · 03-07-2013, 07:02 PM

Can you explain why e21 = -/- 149 ?

132 + 31 = 163

**amdoc** · 03-07-2013, 07:12 PM

The concurrent events for row 21 are rows 18, 19, 21. The missing items -61 - 57 - 31 = -149

Row 20 event had already closed so was not in concurrent events.

**bebo021999** · 03-08-2013, 03:55 AM

Try this:

Please Login or Register  to view this content.

If you know how the code works, it is easier to control this.

**bebo021999** · 03-08-2013, 04:03 AM

May I share the idea?
A2>=A2:A36 give an array with TRUE and FALSE (1 and 0) : i.e 1,1,0,0,1,0,...(A2>=A2,A2>=A3,A2<A4,...)
A2<=B2:B36 ===> i.e: 0,1,0,0,0,0,...
multiply together:
0,1,0,0,0,0,... where A2>=A3 and A2<=B3 gives 1, other gives 0.
SUMPRODUCT: sum all after multiflying.
if multiflying with Missing, SUMPRODUCT gives the total of Missing with the two above criterias.