Skip to main content

Hi!


I am trying to figure out how to do two things that are kinda related. and I am getting no where.



if I have for instance.


3 1 8 2


5 4 4 1


3 7 8 2


2 5 14 2


2 9 14 2



where the first two fields are supplied and the second 2 fields are returned.



column 3 Is the sum of column 2 of all the records what share the same column 1 value.



column 4 is the number of records that share the same value in column 1



and the records would have to be updated as new records are added. so if another row was added with a 3 in in the first column. both record 1 and three would be updated.



can this be done in air table?


any help is really appreciated i’m new to all this.


thanks!

Hey there! Check out this sample base 🙂



Welcome to the community, Michael! 🙂 You’ve definitely got an interesting problem here.





This one can be done with a minor tweak to a solution I came up with a little while ago:







In your case, you want the count to be persistent on all relevant records, rather than increasing with each new instance of a given number/item. To do that, you would skip the third step in my bullet-point breakdown of the process, and use this formula in the next step (the {Substitute} field:



SUBSTITUTE({Lookup A} & "", A & "", "")


In my test, I simply named the first two fields A and B, so this formula replaces all instances of the A number with a blank string. Then the fourth column in your example becomes this formula:



LEN({Lookup A} & "") - LEN(Substitute)


Here’s what I’ve got so far with that in place. The {B Sum} field is where your Column 3 solution will eventually go if I can work it out. It’s more complex than Column 4.





While finishing this post, I saw the reply from @AlliAlosa come through. It uses a similar approach for Column 4, and a clever way to get Column 3 figured out via links. However, I get the impression from your original post that the data in Column 1 would be entered directly, not linked. Please let us know if the link solution will work, or if you need another option.

thank you so much! I just got done with work and just got to look at this, I will mess with it tonight and let you know. this makes a lot of sense though.

Reply


Terms & Conditions