SUMIFS FUNCTION WITH MULTIPLE CRITERIA USING START DATE AND END DATE

[arunknarayan93]

Hello Everyone... 

I've been scouring the internet for a solution to this issue, and it's now my third day at the office,

which is really eating into my time. I could use your help! I'll attach a sample Excel file,

and I'll also explain how I need the report to look.

I have a MS Excel spreadsheet (sheet1) that lists various products in Column A, their amounts in Column B, start dates in Column C, and end dates in Column D. In another spreadsheet (sheet2), I have the same products listed in Column A and the dates arranged in Row 1. I need to calculate the total amount for each product based on the corresponding dates. 

I came across numerous videos and explanations on how to determine the value from a list of dates into a start date and an end date. However, I'm looking for the opposite of that formula. I've attempted several times to crack it, but I haven't been able to figure it out.

I hope I will get a solution here... Thank you guys

SAMPLE FILE 

[#2]

@arunknarayan93 Is this an Excel question or a Google sheets question? Anyway, we do not have access to that file you linked to.

[#3]

@JKPieterse I'm using Microsoft Excel 2021 Version 2410
I had changed the privacy setting of the link I have shared 

[#4 · Accepted solution]

@arunknarayan93 

In B2

=SUMIFS(
   Sheet1!$B$2:$B$11,
   Sheet1!$A$2:$A$11, $A2,
   Sheet1!$C$2:$C$11, "<=" & B$1,
   Sheet1!$D$2:$D$11, ">=" & B$1
)

and drag it to the down and right.

[#5]

@SergeiBaklan 🤗it worked...! Thanks and Lot, you saved me a lot of time on all that manual calculation. 😥

[#6]

@arunknarayan93 , you are welcome, glad to help