Sum values based on two criterias, one horizontal and the other vertical

[Rick__]

Hi,

I'm sorry to post this as I'm sure someone will be able to answer this in a flash. But I have had a look through the forum and online and still couldn't quite find the answer I was looking for, which is the reason I'm reaching out, thank you in advance.

I'm trying create a formula which will sum a set of values based on two criterias, one which is vertical and the other horizontal, see table below.

A	B	C	D	E	F
1	Name	Dept	01/01/18	02/01/18	03/01/18
2	John	IT	1	2	0
3	Sue	IT	1	0	1
4	Paul	HR	2	0	1
5	Steve	HR	1	1	2

<tbody>
</tbody>

I'm trying to sum the values of a Dept on a certain day, so it will look something like the below table.

Date	IT	HR
01/01/18	2	3
02/01/18	2	1
03/01/18	1	3

<tbody>
</tbody>

 

[Rick__]

Aladin, you're a star it worked perfectly thank you again for your support with those formulas.

 

[Aladin Akyurek]

Aladin, you're a star it worked perfectly thank you again for your support with those formulas.

Glad to help.

 

[Rick__]

Hi, sorry all I have another question which is similar, with my data filled out:-

A	B	C	D	E	F
1	Name	01/01/2018	02/01/2018	03/01/2018	04/01/2018
2	John	H	H	H	H
3	Sue	8	8	8	8
4	Paul	8	8	H	H
5	Steve	8	8	8	8

<tbody>
</tbody>

I would like to only count or sum a certain date range from C1 to E1 below so I get the results of the table below.

Going forward I want to be able to each month to change the date range which will then update the formula.

A	B	C	D	E
1	Date From	02/01/2018	Date To	04/01/2018
2	Name	Hours	H's
3	John	0	3
4	Sue	24	0
5	Paul	8	2
6	Steve	16	0

<tbody>
</tbody>

Thank you in advance

 

[Aladin Akyurek]

Book1
	A	B	C	D	E
1	Name	1/1/2018	2/1/2018	3/1/2018	4/1/2018
2	John	H	H	H	H
3	Sue	8	8	8	8
4	Paul	8	8	H	H
5	Steve	8	8	8	8
Sheet1

Book1
	A	B	C	D
1	Date From	2/1/2018	Date To	4/1/2018
2	Name	Hours	H's
3	John	0	3
4	Sue	24	0
5	Paul	8	2
6	Steve	24	0
Sheet2

In B3 of Sheet2 enter and copy down:

=SUMIFS(INDEX(Sheet1!$B$2:$E$5,MATCH($A3,Sheet1!$A$2:$A$5,0),0),Sheet1!$B$1:$E$1,">="&B$1,Sheet1!$B$1:$E$1,"<="&D$1)

In C3 of Sheet2 enter and copy down:

=COUNTIFS(INDEX(Sheet1!$B$2:$E$5,MATCH($A3,Sheet1!$A$2:$A$5,0),0),"H",Sheet1!$B$1:$E$1,">="&B$1,Sheet1!$B$1:$E$1,"<="&D$1)