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

 

[ExceLoki]

i did, i made sure all are formatted as dates

 

[Gerald Higgins]

Not just formated as dates, are they all really numbers ?
It's possible to have a text string, that is formated as a date, but still Excel treats it as a text string.
You can test this with a formula like
=istext(a1)
If the result is TRUE, you have a text string, not a date, even if it looks like a date.

I can't be certain that this is your problem, but it does seem like the most likely one.

 

[ExceLoki]

so what i ended up doing was adding a helper column to change the date from the pivot table to an actual date.
slight adjustment to the formula and it works great!

 

[Gerald Higgins]

Yay, well done !

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[ExceLoki]

thanks to both of you for the help. i know i've done these before, but was getting stuck and frustrated.