Match name and return sum between date range

[ExceLoki]

i'm trying to return a sum value from a range of data by matching a name and between a set of dates. i've got the dates in the results sheet in two columns.
i'm stuck, any help is appreciated. and for some reason xl2bb keeps freezing my excel so i had to just copy the sheets

here is what i was trying to use in sheet7 column I:

=SUMIFS(Sheet6!$C$5:$Z$8,Sheet6!2:2,">="&A2,Sheet6!2:2,"<="&B2,Sheet6!A:A,D2)

here is a sample of data, coulmn A is a lookup for the name matching the email. the email and the rest is from a pivot table:
Sheet6

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

Name

Row Labels

04/01/2022

04/02/2022

04/04/2022

04/05/2022

04/06/2022

04/07/2022

04/08/2022

04/11/2022

04/12/2022

04/13/2022

04/14/2022

04/15/2022

04/18/2022

04/19/2022

04/20/2022

04/21/2022

04/22/2022

04/23/2022

04/25/2022

04/26/2022

04/27/2022

04/28/2022

04/29/2022

04/30/2022

Bob Smith

Bob.Smith@company.com

11​

9​

9​

8​

1​

9​

9​

6​

5​

6​

7​

3​

7​

8​

5​

11​

10​

1​

Jane Doe

Jane.Doe@company.com

5​

9​

5​

8​

5​

6​

4​

8​

8​

1​

2​

4​

5​

8​

1​

1​

1​

2​

1​

2​

3​

5​

9​

Bob Doe

Bob.Doe@company.com

1​

2​

1​

1​

7​

8​

1​

2​

1​

4​

1​

7​

1​

2​

2​

4​

9​

3​

Jane Smith

Jane.Smith@company.com

2​

1​

3​

7​

9​

6​

4​

11​

1​

1​

3​

1​

4​

8​

9​

7​

9​

4​

here is a sample of the result:
Sheet7

A	B	C	D	E	F	G	H	I
Start Date	End Date	Report Ran	Full Name	field 1	field 2	field 3	field 4	Cases Worked
4/1/2022	4/7/2022	4/9/2022	Bob Smith					37
4/1/2022	4/7/2022	4/9/2022	Jane Doe					38
4/1/2022	4/7/2022	4/9/2022	Bob Doe					3
4/1/2022	4/7/2022	4/9/2022	Jane Smith					13

 

[Fluff]

You will need to use sumproduct for that.

=sumproduct((Sheet6!2:2>=A2)*(Sheet6!2:2<=B2)*(Sheet6!A:A=D2)*(Sheet6!$C$5:$Z$8))

although you would be better off not using entire column/row references.

 

[Gerald Higgins]

Or maybe like this . . .

=SUMIFS(OFFSET(Sheet6!$C$2,MATCH(D2,Sheet6!A3:A6,0),0,1,24),Sheet6!C2:Z2,">="&A2,Sheet6!C2:Z2,"<="&B2)

Edited to add - I've missed out some $ symbols here, you'll need to sprinkle a few of those in here

 

[ExceLoki]

ok, now xl2bb is working for me. in the last replies the formulas returned #NA

case tracker-WIP.xlsx
	A	B	C	D	E	F	G	H	I
1	Start Date	End Date	Report Ran	Full Name	field 1	field 2	field 3	field 4	Cases Worked
2	4/1/2022	4/7/2022	4/9/2022	Bob Smith					37
3	4/1/2022	4/7/2022	4/9/2022	Jane Doe					38
4	4/1/2022	4/7/2022	4/9/2022	Bob Doe					3
5	4/1/2022	4/7/2022	4/9/2022	Jane Smith					13
Sheet7

Cell Formulas
Range	Formula
B2:B5	B2	=A2+6
C2:C5	C2	=B2+2

-----------------

case tracker-WIP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Name	Row Labels	04/01/2022	04/02/2022	04/04/2022	04/05/2022	04/06/2022	04/07/2022	04/08/2022	04/11/2022	04/12/2022	04/13/2022	04/14/2022	04/15/2022	04/18/2022	04/19/2022	04/20/2022	04/21/2022	04/22/2022	04/23/2022	04/25/2022	04/26/2022	04/27/2022	04/28/2022	04/29/2022	04/30/2022
2	Bob Smith	Bob.Smith@company.com	11	9		9		8	1		9	9		6		5	6	7		3	7	8	5	11	10	1
3	Jane Doe	Jane.Doe@company.com	5	9	5	8	5	6	4	8		8	1	2	4	5	8	1	1	1	2	1	2	3	5	9
4	Bob Doe	Bob.Doe@company.com	1					2	1	1	7	8		1	2	1	4	1	7		1	2	2	4	9	3
5	Jane Smith	Jane.Smith@company.com		2	1		3	7	9	6	4		11		1	1		3		1	4	8	9	7	9	4
Sheet6

 

[Fluff]

With that layout, this worked for me

=SUMPRODUCT((Sheet6!$C$1:$Z$1>=A2)*(Sheet6!$C$1:$Z$1<=B2)*(Sheet6!$A$2:$A$100=D2)*(Sheet6!$C$2:$Z$100))

 

[Gerald Higgins]

Well I'm not getting #N/A errors.
Here's a slightly different version, with the $ symbols added in.
=SUMIFS(OFFSET(Sheet6!$C$2,MATCH(D2,Sheet6!A$3:A$6,0),0,1,24),Sheet6!C$1:Z$1,">="&A2,Sheet6!C$1:Z$1,"<="&B2)
Is this exactly what you have in I2 ?

Edit - I originally had the dates on Sheet6 in Row 2, I've changed that now to Row 1.

 

[ExceLoki]

now both are returning 0 values. the current values in column I on this example are manually entered.
@Gerald - i had to edit yours to look at the range of names
----------------

case tracker-WIP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Start Date	End Date	Report Ran	Full Name	field 1	field 2	field 3	field 4	Cases Worked	FLUFF	Gerald-edit	Gerald
2	4/1/2022	4/7/2022	4/9/2022	Bob Smith					37	0	0	#N/A
3	4/1/2022	4/7/2022	4/9/2022	Jane Doe					38	0	0	0
4	4/1/2022	4/7/2022	4/9/2022	Bob Doe					3	0	0	0
5	4/1/2022	4/7/2022	4/9/2022	Jane Smith					13	0	0	0
6	4/8/2022	4/14/2022	4/16/2022	Bob Smith					19	0	0	#N/A
7	4/8/2022	4/14/2022	4/16/2022	Jane Doe					21	0	0	0
8	4/8/2022	4/14/2022	4/16/2022	Bob Doe					17	0	0	0
9	4/8/2022	4/14/2022	4/16/2022	Jane Smith					30	0	0	0
10	4/15/2022	4/21/2022	4/23/2022	Bob Smith					24	0	0	#N/A
11	4/15/2022	4/21/2022	4/23/2022	Jane Doe					21	0	0	0
12	4/15/2022	4/21/2022	4/23/2022	Bob Doe					9	0	0	0
13	4/15/2022	4/21/2022	4/23/2022	Jane Smith					5	0	0	0
14	4/22/2022	4/28/2022	4/30/2022	Bob Smith					34	0	0	#N/A
15	4/22/2022	4/28/2022	4/30/2022	Jane Doe					10	0	0	0
16	4/22/2022	4/28/2022	4/30/2022	Bob Doe					16	0	0	0
17	4/22/2022	4/28/2022	4/30/2022	Jane Smith					29	0	0	0
18	4/29/2022	5/5/2022	5/7/2022	Bob Smith					11	0	0	#N/A
19	4/29/2022	5/5/2022	5/7/2022	Jane Doe					14	0	0	0
20	4/29/2022	5/5/2022	5/7/2022	Bob Doe					12	0	0	0
21	4/29/2022	5/5/2022	5/7/2022	Jane Smith					13	0	0	0
Sheet7

Cell Formulas
Range	Formula
B2:B21	B2	=A2+6
C2:C21	C2	=B2+2
J2:J21	J2	=SUMPRODUCT((Sheet6!$C$1:$Z$1>=A2)*(Sheet6!$C$1:$Z$1<=B2)*(Sheet6!$A$2:$A$100=D2)*(Sheet6!$C$2:$Z$100))
K2:K21	K2	=SUMIFS(OFFSET(Sheet6!$C$2,MATCH(D2,Sheet6!A$2:A$6,0),0,1,24),Sheet6!C$2:Z$2,">="&A2,Sheet6!C$2:Z$2,"<="&B2)
L2:L21	L2	=SUMIFS(OFFSET(Sheet6!$C$2,MATCH(D2,Sheet6!A$3:A$6,0),0,1,24),Sheet6!C$2:Z$2,">="&A2,Sheet6!C$2:Z$2,"<="&B2)
A6,A10,A14,A18	A6	=B5+1
A7,A11,A15,A19	A7	=A6
A8,A12,A16,A20	A8	=A6
A9,A13,A17,A21	A9	=A6

 

[Fluff]

Check that all the dates are real dates & not text.

 

[ExceLoki]

does it matter that sheet 6 is a pivot table? maybe that's why it's not working correctly?

 

[Gerald Higgins]

I don't think a pivot table should make any difference.

Did you do what Fluff suggested in post #8 ?
That could definitely make a difference, if some values are not really dates but just text strings.