Sum value between two dates with certain criteria.

[premjeetgupta]

I need an excel expert
I am attaching a sample file.
Sheet1 contains the data table
In sheet2, you may find some examples I need a formula for
Basically I need a formula that given the value, the start date and end date, it returns me the sum of the cells in sheet1 that match those conditions.

Thanks in advance.

 

[premjeetgupta]

I could able to create the formula. But the formula is little large to understand for some.
Can you help with some short formula for required result.

 

[pecoflyer]

In G2 enter
=SUMPRODUCT(--(Sheet1!$A$2:$A$321>=Sheet2!$C2),--(Sheet1!$A$2:$A$321<=Sheet2!$D2)*INDEX(Sheet1!$A$2:$H$321,,MATCH(Sheet2!$B2;Sheet1!$A$1:$H$1,0)))
and pull down
Be aware that the double comma is NOT a typo

 

[premjeetgupta]

Thanks @pecoflyer for your response.
I just copied and pasted in column "G2" but that is giving error result.
Can you check what i missed.

 

[pecoflyer]

I forgot to change a semi colon into a comma
=SUMPRODUCT(--(Sheet1!$A$2:$A$321>=Sheet2!$C2),--(Sheet1!$A$2:$A$321<=Sheet2!$D2)*INDEX(Sheet1!$A$2:$H$321,,MATCH(Sheet2!$B2,Sheet1!$A$1:$H$1,0)))

 

[premjeetgupta]

Thanks @pecoflyer for quick reply.

 

[pecoflyer]

You're welcome

 

[premjeetgupta]

Hi @pecoflyer , Can you please help me to understand this formula. How this working. I've tried to understand it by splitting the formula but the expected result not coming. Sorry for bothering you.

Thanks in advance.

 

[pecoflyer]

Congratulations for trying to understand the solution ! Most people just absorb the result and move on

The basics of SUMPRODUCT are clearly explained at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
As you can see this function works on ranges
The difficulty here is to define the range with a specific header, and that is the job of the INDEX function which, embedded in another function, returns a range reference for a column or a row when the match value is replaced by a double comma.
There should be better explanations on the Net, but the advanced use of INDEX is not always well documented and I haven't found one yet
If you find a link to a good explanation, please let me know
Cheers