Is there a way to automatically generate calculations to adjacent cell without dragging the functions?

[xaikus506]

Hello, I would like to ask if there is a way to generate calculations next to adjacent arrays such as Unique function, Filter function without automatically without dragging down functions in excel or without utilizing tables or preferably without using offset function or vba?

For example in this image the number of Job Titles can Decrease or increase and I want generate a countifs without spilling the entire formula to the entire column. Is this possible? Thank you for your help!

 

[kevin9999]

Hi Kevin the formula on the earlier post work if the adjacent cell is an array based formula. but I had a problem making it work if my reference is a raw data that is not an array. the "#" doesnt seem to work anymore

Yes, that's correct, but it looks like you've had a number of good suggestions by people more expert than me since.

 

[xaikus506]

Hi Peter, Thanks for the response, I am trying your formula with the function but in combination of formulas such as =TAKE(TRIM(CONCATENATE(C5,","," ",A5," ",B5)),COUNTA(C:C)-3) and =INDEX(NUMBERVALUE(TRIM(D5)),SEQUENCE(COUNTA(D:D)-3)). I have a trouble making it work. the Take function with the CountA does not return an array and the Index and Sequence function returns an array the 1st entry is correct but the cells below generate a Ref error

Hi this is a screenshot of what I want to happen without the reference error

 

[xaikus506]

Hi this is a screenshot of what I want to happen without the reference error
View attachment 77703View attachment 77704

Oh I Figured it out, my mistake in the index parameter! thank you so much for the help everyone

 

[Peter_SSs]

Oh I Figured it out, my mistake in the index parameter! thank you so much for the help everyone

You're welcome. Glad we could help.

BTW, I have changed the 'Mark as solution' from post #7 which you had marked as the solution to your follow-up question, to post #2 which contained the solution to your original question. This will be more help to future readers which is what the 'Mark as solution' is designed for.