Let and SUM values

[Redlad91]

Hi,
I previously received some support on the following link Formula Support - Top 10 list using Index and Match, where i was directed to changing my formula to a LET function.

The formula worked fine, but i noticed that it was pulling back multiple values with the same name, Is there anyway that i can add sum into the formula so it pulls through the total for a specific item number. for example, using the same table in the previous question, I want to sum up when a colleague with the same ID has worked multiple hours of overtime.

Data Table

Colleague ID	Age	Name	Department	Overtime Worked
12333	26	Lewis	HR	5
12334	27	Ryan	Finance	5
12333	26	Lewis	HR	2
12336	26	Lisa	IT	2
12337	26	Joanne	Finance	1
12336	26	Lisa	IT	4
12339	23	Brian	HR	3
12340	27	Luke	Finance	2
12341	26	James	IT	2

with my current formula, it would show both values rather than summarising them. for example if i show from the above results, it would appear like this

12333	26	Lewis	HR	5
12336	26	Lisa	IT	4
12333	26	Lewis	HR	2
12336	26	Lisa	IT	2

Whereas i would want it to show as

12333	26	Lewis	HR	7
12336	26	Lisa	IT	6

My current formula is =LET(f,SORT(FILTER(A2:D10,D2:D10="HR"),2,-1),INDEX(f,SEQUENCE(MIN(ROWS(f),15)),{1,2,3,4})) and this is where i want SUM adding into.

I have tried adapting the f at the start and summing in there but can't seem to get it to work.

Thanks in advance for any support

 

[Fluff]

You cannot be using the formula you posted as it only looks at 4 columns, not 5 & it's filtering on HR but your results show IT as well.

 

[Redlad91]

You cannot be using the formula you posted as it only looks at 4 columns, not 5 & it's filtering on HR but your results show IT as well.

Apologies i was meant to only have HR in the data.

Essentially i'm looking to summarise anything that meets the criteria in the formula, So in this instance the formula would be

=LET(f,SORT(FILTER(A2:D10,D2:D10="HR"),2,-1),INDEX(f,SEQUENCE(MIN(ROWS(f),15)),{1,2,3,4,5}))

So i would want anything that has HR in the Department column and then summarise the overtime column, then sort them in Highest to lowest to get back the below.

12333	26	Lewis	HR	7
12336	26	Lisa	HR	6

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Colleague ID	Age	Name	Department	Overtime Worked	No	Colleague ID	Age	Name	Department
2	12333	26	Lewis	HR	5	1	12339	23	Brian	HR	8
3	12334	27	Ryan	Finance	5	2	12333	26	Lewis	HR	7
4	12333	26	Lewis	HR	2	3
5	12336	26	Lisa	IT	2
6	12337	26	Joanne	Finance	1
7	12336	26	Lisa	IT	4
8	12339	23	Brian	HR	8
9	12340	27	Luke	Finance	2
10	12341	26	James	IT	2
11
Data

Cell Formulas
Range	Formula
G2:K3	G2	=LET(f,SORT(FILTER(A2:D10,D2:D10="HR"),2,-1),u,UNIQUE(f),SORT(IF(SEQUENCE(,5)<=4,u,SUMIFS(E2:E10,A2:A10,INDEX(u,,1))),5,-1))
Dynamic array formulas.