Excel: Need to sum based on date range in two columns (between dates) over one column

Question

My raw data is this:

My desired result is this:

Explanation of desired result:

So, for example if 01/07/2018 falls between the date from and date to and it's under package A, we will get the number and finally total them together. So as you can see in my raw data, 01/07/2018 belongs to Mr.X and Ms.Z, so the total sum under package A for 01/07/2018 is 2. The next date, 02/07/2018 and under package A, we have Mr.X only so the total sum is 1.

I've tried the following formula:

=SUMIFS(D3:D9, B3:B9, ">=" &J3, C3:C9, "<=" &J3)

But it just resulted in 0. How can I get the correct result as shown in my desired result?

J3 is just a dummy column with a date. For e.g 01/07/2018. D3:D9 is column package A, B3:B9 is column date from, C3:C9 is column date to

Answer 1

You just need to reverse the < and > signs:

=SUMIFS(D3:D9, B3:B9, "<=" &J3, C3:C9, ">=" &J3)

then put some dollar signs in so it will give the right answer when you pull it across and down

=SUMIFS(D$3:D$9, $B$3:$B$9, "<=" &$J3, $C$3:$C$9, ">=" &$J3)