SUMIFS between 2 Dates

[s0nicstang]

Hello,

I am trying to figure out how to sum data using with date range as one of the variables. Col A has my customer name and across row 1 are my dates. I want to see how much each customer purchased between 2 given dates.

Here is how my data looks

customer	6/1/16	6/2/16	6/3/16	6/4/16
customer1	5	10	10	0
customer2	50	140	150	10
customer3	57	140	160	70
customer4	15	430	340	32

So say I want to know how much customer 1 spent between 6/1/16 and 6/3/16

Any thoughts?

Thanks!

[oeldere]

the data is in a220 : E224

g218 = 6-1-2016
g219 = 6-3-2016

g221 =SUMPRODUCT(($B$220:$E$220>$G$218)*($B$220:$E$220<$G$219)*($B221:$E221)) and drag down.

The result is:

10
140
140
430

[JohnTopley]

Assuming dates in row 1 and data in row 2

=SUMIFS($B2:$F2,$B$1:$F$1,">=" &B1,$B$1:$F$1,"<=" &D1)

B1 is start date, D1 is finish date

[s0nicstang]

Is there a way to add the customer variable in there as well as a cell?

So
g218 = 6-1-2016
g219 = 6-3-2016
g220 = Customer 1

I am trying to get this data on a summary page so it would be preferred not to have the summed data right next to the raw data. Does that make sense?

[JohnTopley]

Try

=SUMPRODUCT(($B$2:$F$5)*($B$1:$F$1>=B$1)*($B$1:$F$1<=D$1)*($A$2:$A$5=$A2))

Customer names in A2 down

Data in B2:F5

Drag formula down

[Tony Valko]

Here's another one...

Data Range

	A	B	C	D	E
1	customer	6/1/2016	6/2/2016	6/3/2016	6/4/2016
2	customer1	5	10	10	0
3	customer2	50	140	150	10
4	customer3	57	140	160	70
5	customer4	15	430	340	32
6
7
8	6/1/2016	6/3/2016	Customer1	25

This formula entered in D8:

=SUM(INDEX(B2:E5,MATCH(C8,A2:A5,0),MATCH(A8,B1:E1,0)):INDEX(B2:E5,MATCH(C8,A2:A5,0),MATCH(B8,B1:E1,0)))