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.



[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]Dept
[/TD]
[TD]01/01/18
[/TD]
[TD]02/01/18
[/TD]
[TD]03/01/18
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]John
[/TD]
[TD]IT
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Sue
[/TD]
[TD]IT
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Paul
[/TD]
[TD]HR
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Steve
[/TD]
[TD]HR
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
</tbody>[/TABLE]

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


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]IT
[/TD]
[TD]HR
[/TD]
[/TR]
[TR]
[TD]01/01/18
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]02/01/18
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]03/01/18
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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:-


[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]01/01/2018
[/TD]
[TD]02/01/2018
[/TD]
[TD]03/01/2018
[/TD]
[TD]04/01/2018
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]John
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Sue
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Paul
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Steve
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Date From
[/TD]
[TD]02/01/2018
[/TD]
[TD]Date To
[/TD]
[TD]04/01/2018
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Name
[/TD]
[TD]Hours
[/TD]
[TD]H's
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]John
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Sue
[/TD]
[TD]24
[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Paul
[/TD]
[TD]8
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Steve
[/TD]
[TD]16
[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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)