Summing data by name

[DitdotUK]

Hi All,

I'm not an excel expert and I have limited knowledge.

I have two sheets of data.

The first sheet of Data looks like this

[TABLE="width: 200"]
<tbody>[TR]
[TD]Name[/TD]
[TD]CallIN[/TD]
[TD]Abandoned[/TD]
[TD]Outbound[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]30[/TD]
[TD]3[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]20[/TD]
[TD]7[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]15[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]3[/TD]
[TD]17[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Mo[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On another sheet I want excel to return

[TABLE="width: 200"]
<tbody>[TR]
[TD]Name[/TD]
[TD]CallIN[/TD]
[TD]Abandoned[/TD]
[TD]Outbound[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]55[/TD]
[TD][/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

I've tried using vlookup with sum ifs

=SUM(VLOOKUP(A3,'User Productivity Summary'!C:M,{3,9},FALSE))

But this will only count the first row of each user in my source data.

Thanks

Paul

 

[RoryA]

Welcome to the forum.

It looks like a pivot table would produce exactly what you need.

 

[RasGhul]

Hi Paul,

You can copy across and double click down;

=SUMIFS(Sheet1!B$2:B$11,Sheet1!$A$2:$A$11,$A2)

https://www.youtube.com/watch?v=SQUbL02XvGw

 

[DitdotUK]

Hi Paul,

You can copy across and double click down;

=SUMIFS(Sheet1!B$2:B$11,Sheet1!$A$2:$A$11,$A2)

https://www.youtube.com/watch?v=SQUbL02XvGw

Thank you - the problem is that the user name changes position in the source data depending on the day and if they are logged in

 

[DitdotUK]

Is it possible to to do sum if on two ranges?

Sorry to be a pain!

 

[RasGhul]

You could add a date column to your source data which can be added to your sumifs criteria?

 

[RasGhul]

Is it possible to to do sum if on two ranges?

Sorry to be a pain!

Yes you can do multiple criteria with sumifs. Did you only want to calculate values of the current date?

 

[DitdotUK]

Hi So I want my creteria to be C:C,D:D,E:E and I want the sum if to sum all values in the rage for a user

 

[RasGhul]

Assuming you have your users in column A, Copy across and drag down;

=SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2)

 

[jtakw]

Hi,

If I understand correctly:

Book1
	A	B	C	D
1	Name	CallIN	Abandoned	Outbound
2	Joe	65	12	39
3	Ben	11	19	15
4	Mo	12	15	7
Sheet107

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(('User Productivity Summary'!$B$2:$B$500=$A2)*('User Productivity Summary'!$C$1:$E$1=B$1)*'User Productivity Summary'!$C$2:$E$500)

Formula copied down and across, adjust Cell References/Ranges as necessary.

Book1
	B	C	D	E
1	Name	CallIN	Abandoned	Outbound
2	Joe	30	3	25
3	Joe	20	7	12
4	Joe	15	2	2
5	Ben	3	17	0
6	Ben	8	2	15
7	Mo	12	15	7
User Productivity Summary