Sum for date range where columns are individual days and criteria can be in multiple rows

leonp

New Member
Joined
Sep 9, 2016
Messages
19
Hello,

I’m trying to determine if there is a formula I can use that will be able to dothis.

The columns in my spreadsheet represent individual dates(e.g. column B is 01/09, C is 02/09, etc). for each date there are quantities andin column A there is a product code. Each product code can repeat in multiple rows.

If that is sheet1 and in sheet2, I have a from date (let’ssay in A1) and a to date (A2), is there a way to sum the total quantity for agiven product code based on the date range entered? I.e. I will need to sum every occurrence ofthe product in multiple columns.

Thanks!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
With sheet1 like


Book1
ABCDEF
1Product01/09/201902/09/201903/09/201904/09/201905/09/2019
2E05004799
3E05004785123
4E05004802
5E05004787
6E05004785123
7E05011061
Sheet1


In sheet2


Book1
AB
1
201/09/201902/09/2019
3E050047856
Sheet2
Cell Formulas
RangeFormula
B3=SUMPRODUCT((Sheet1!B1:T1>=A2)*(Sheet1!B1:T1<=B2)*(Sheet1!A2:A15=A3),Sheet1!B2:T15)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top