Help with formula consolidating Data

[Newbie73]

Hello everyone,

I need some help with a formula that I can't get my head around it. Example sheet provided:

test1.xlsx

Column A has IDs which often will be duplicated. Column B has a number from a test specific to the ID on Column A of that row and then Column C dates for those tests.

So what I would like to do is to consolidate these data as in, all the results for each ID from Column A sorted per year and If more than one result (column B) different than zero from the same year then sum the value.

There's a wanted result on the example sheet which hopefully it will help.

Thank you in advance for your help and please do tell if I didn't explain something correctly or if you need any more informations.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Newbie73]

Took me awhile as I didn't want to bother again, and results wise seems to be okay but could you just double check if you could if I did this right or if there is a more efficient way, I wanted the zero values to show so I editted your formula to this:

=LET(f,HSTACK(A2:A10000,B2:B10000,YEAR(C2:C10000),D2:D10000),u,UNIQUE(HSTACK(CHOOSECOLS(f,1,3))),b,BYROW(u,LAMBDA(br,SUM(IF((INDEX(f,,1)=INDEX(br,,1))*(INDEX(f,,3)=INDEX(br,,2)),INDEX(f,,2))))),c,BYROW(u,LAMBDA(br,SUM(IF((INDEX(f,,1)=INDEX(br,,1))*(INDEX(f,,3)=INDEX(br,,2))*((INDEX(f,,4)="ir")+(INDEX(f,,4)="r")),1,0)))),CHOOSECOLS(HSTACK(u,b,c),1,3,4,2))

 

[Fluff]

If it does what you want, then it's correct.