Problem with LET function

Question

My intended post apparently exceeded 6000 characters and was refused.

So now I ask if someone is willing to take a look at

https://www.dropbox.com/scl/fi/lh716d3l6ghlvz33hmnxn/Problems_with_LET.xlsx?rlkey=1hq8rfpv65dy29mu1g6v30e14&dl=0

and hopefully help me to create a LET formula.

Answer 1

F2: =LET(u,UNIQUE(d[Name]),

f,MINIFS(d[Idx],d[Name],u),

t,MAXIFS(d[Idx],d[Name],u),

j,UNIQUE(d[Job]),

h,SUMIF(d[Name],u,d[Hours]),

i,IF(f=t,f,f&"-"&t),

HSTACK(u,j&", "&i,h))

Sample file
https://1drv.ms/x/s!AsEpmlJLteaskw-LkkXpcGfrvmpf?e=yGhG3Q

Answer 2

Hi,

You could use SUMIF with your array of unique names as criteria:

=LET(u,UNIQUE(d[Name]),

m,UNIQUE(d[Job])&", "&MINIFS(d[Idx],d[Name],u)&"-"&MAXIFS(d[Idx],d[Name],u),

h,SUMIF(d[Name],u,d[Hours]),

a,HSTACK(u,m,h),

a

)

Answer 3

No, they were there - you just need to resize the column.

Answer 4

@Rory Archibald

Thank you very much.

Almost what I wanted. I only miss the Idx-number after job title (1-3 for Mechanic, 4-4 or just 4 for Bricklayer etc.)

Answer 5

@Andreas Killer

Again, again you give me exactly what I wanted, Thank you very much.

Hans K.