Sum of Value overlap dates criteria

[qfreud]

Dear Excel Expert,

My problem,
based on long term contract data with start date, end date and monthly revenue (be simplified),
how I summarize with a choice of monthly periods (2, 3, 4, 6 months & yearly)?

Start End Monthly
1/4/16 31/3/20 1.000

Criteria Option
Start : 1/1/16, End : 31/3/16 ( 3months period) or
Start : 1/1/16, End : 30/4/16 ( 4months period), etc..

The following sample data attachment,

I only figure out monthly periods

Please Login or Register  to view this content.

Thank you very much in advance
qFreud

[tamthat]

Dear Excel Expert,

My problem,
based on long term contract data with start date, end date and monthly revenue (be simplified),
how I summarize with a choice of monthly periods (2, 3, 4, 6 months & yearly)?

Start End Monthly
1/4/16 31/3/20 1.000

Criteria Option
Start : 1/1/16, End : 31/3/16 ( 3months period) or
Start : 1/1/16, End : 30/4/16 ( 4months period), etc..

The following sample data attachment,

I only figure out monthly periods

Please Login or Register  to view this content.

Thank you very much in advance
qFreud

Try it:

E12=SUM((DATA!$A$4:$A$10=$E$4)*(DATA!$C$4:$C$10>=E$8)*(DATA!$C$4:$C$10<=E$9)*(DATA!$B$4:$B$10=$E$5)*DATA!$G$4:$G$10/$E$3*$E$2) 

Ctrl + Shift + Enter!

[qfreud]

thanks for your suggestion tamthat

but my problem :
lets say an example
Start date "1 Mar 2016" to End date "1 Mar 2019" with monthly revenue 2000,

Criteria dates
From "1 Jan 2016" to "1 Jul 2016",
it means including Mar, Apr, & Jun revenue

how the formula to calculate/sum with these criteria?

[JeteMc]

This solution employs three helper rows, which may be hidden for aesthetic purposes, as well as modifying the formula originally in row 12. The modified formula reads:

Please Login or Register  to view this content.

The formula for the first helper row, which counts the number of months between rows 8 and 9 is:

Please Login or Register  to view this content.

The array entered formulas* that helps to find the number of months during that period are actually under contract are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let me know if you have any questions.

[qfreud]

JeteMc

Wow
that I was looking for

But seems like the "sum" work only top row "user" criteria,

Well, I need to figure out just one more step

[JeteMc]

Not sure that I understand what you mean by "Work only for top row 'user' criteria". Does this mean that you want to uses formulas to fill in the table in rows 18:23? I was under the impression that was just a table of expected values for user A2.