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

Name	CallIN	Abandoned	Outbound
Joe	30	3	25
Joe	20	7	12
Joe	15	2	2
Ben	3	17	0
Ben	8	2	15
Mo	12	15	7

<tbody>
</tbody>

On another sheet I want excel to return

Name	CallIN	Abandoned	Outbound
Joe	55		39
Ben	11		15
Joe	12		7

<tbody>
</tbody>

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