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]

 

[Special-K99]

With your first table on Sheet1 and your output table on Sheet 2 and Sheet2!A1=Date
Put your dates in Sheet2 column A
Put your Depts in Sheet2 row 1
So you ve now got headers in Sheet 2column A and row 1

in Sheet2!B2
=SUMPRODUCT((Sheet1!C$2:C$5=B$1)*(Sheet1!$D$1:$F$1=$A2)*(Sheet1!$D$2:$F$5))
and copy across and down

 

[Rick__]

thats perfect thank you

 

[Rick__]

I had another question based on the similar data set, on the first sheet I have to sum had numbers as well as text/Letter, how could I get round this.

1. I would like just add all the numbers together.

2. depending on the text/Letter, I would need to count the number of times a particular letter appears in a column.

Thank you in advance

 

[Special-K99]

Can you supply some example data?

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Dept	1/1/2018	2/1/2018	3/1/2018		Date	IT	HR
2	John	IT	1	2	0		1/1/2018	2	3
3	Sue	IT	1	0	1		2/1/2018	2	1
4	Paul	HR	2	0	1		3/1/2018	1	3
5	Steve	HR	1	1	2
Sheet1

In H2 enter, copy across, and down:

=SUMIFS(INDEX($C$2:$E$5,0,MATCH($G2,$C$1:$E$1,0)),$B$2:$B$5,H$1)

 

[Rick__]

Apologies for the late reply, thats perfect thank you.

 

[Aladin Akyurek]

Apologies for the late reply, thats perfect thank you.

You are very welcome. Thanks for the update.

 

[Rick__]

I just had one more question on the same senario, I'm trying to do the same senario above but count the number of times a letter "H" appears in the data.

I've tried to convert the SUMIFS fourmula below to a COUNTIFS but it hasn't worked, thank you in advance.

=SUMIFS(INDEX($C$2:$E$5,0,MATCH($G2,$C$1:$E$1,0)),$B$2:$B$5,H$1)

 

[Aladin Akyurek]

I just had one more question on the same senario, I'm trying to do the same senario above but count the number of times a letter "H" appears in the data.

I've tried to convert the SUMIFS fourmula below to a COUNTIFS but it hasn't worked, thank you in advance.

=SUMIFS(INDEX($C$2:$E$5,0,MATCH($G2,$C$1:$E$1,0)),$B$2:$B$5,H$1)

Do you mean the following?


=COUNTIFS(INDEX($C$2:$E$5,0,MATCH($G2,$C$1:$E$1,0)),"H",$B$2:$B$5,H$1)