SUMPRODUCT where between 2 dates

[alpha.martinez]

Hi there!
I as pointed to this group as the best place to ask this question. Thanks in advance for the help!

How would you get a report for Item total sales per date range with an excel file in the following format;
The idea is to have another table with the item # and a date range to use as parameters to then output a total per item, per date

Date Item 1 Item 2 Item 3 Item 4 Item 5 Item 6
1-Jan 60 50 48 72 75 43
2-Jan 52 40 57 51 72 20
3-Jan 46 35 47 38 68 29
4-Jan 35 33 33 43 48 39
5-Jan 46 51 25 30 40 28
6-Jan 60 50 48 72 75 43
7-Jan 52 40 57 51 72 20
8-Jan 46 35 47 38 68 29
9-Jan 35 33 33 43 48 39
10-Jan 46 51 25 30 40 28
11-Jan 60 50 48 72 75 43
12-Jan 52 40 57 51 72 20
13-Jan 46 35 47 38 68 29
14-Jan 35 33 33 43 48 39
15-Jan 46 51 25 30 40 28
16-Jan 60 50 48 72 75 43
17-Jan 52 40 57 51 72 20
18-Jan 46 35 47 38 68 29
19-Jan 35 33 33 43 48 39
20-Jan 46 51 25 30 40 28
21-Jan 60 50 48 72 75 43
22-Jan 52 40 57 51 72 20
23-Jan 46 35 47 38 68 29
24-Jan 35 33 33 43 48 39
25-Jan 46 51 25 30 40 28
26-Jan 60 50 48 72 75 43
27-Jan 52 40 57 51 72 20
28-Jan 46 35 47 38 68 29
29-Jan 35 33 33 43 48 39
30-Jan 46 51 25 30 40 28

 

[Claus Busch]

Hi,

Am Tue, 4 Jun 2013 12:54:12 -0700 (PDT) schrieb
(e-mail address removed):

How would you get a report for Item total sales per date range with an excel file in the following format;
The idea is to have another table with the item # and a date range to use as parameters to then output a total per item, per date

in Sheet2 A2 the start date, in sheet2 A3 the end date.
The item # in B1 to G1
Then try in B2:
=SUMPRODUCT(--(Sheet1!$A$2:$A$31>=$A$2),--(Sheet1!$A$2:$A$31<=$A$3),Sheet1!B2:B31)
for Item1 and copy to the right


Regards
Claus Busch

 

[Alberto Martinez]

Hi,



Am Tue, 4 Jun 2013 12:54:12 -0700 (PDT) schrieb









in Sheet2 A2 the start date, in sheet2 A3 the end date.

The item # in B1 to G1

Then try in B2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$31>=$A$2),--(Sheet1!$A$2:$A$31<=$A$3),Sheet1!B2:B31)

for Item1 and copy to the right





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thank you very much! That is exactly what I was looking for.

 

[Alberto Martinez]

Thank you very much! That is exactly what I was looking for.