Formula to combine QTY

[Alex_Aguilar]

I wanted to use a formula to combine these unique QTY’s across the rows. Could i use one formula that would be able to combine all these rows into a singular QTY for their unique groups? For example Kevin has two unique dates one with a combined QTY of 6 and the other with 9, whereas AJ has a unique QTY of 4.

[David_Skinner]

You need to have a 2nd table for the People. So it would have 2 records, AJ and Kevin in it (plus any other people you might require). You can then link the events in the table above to the records in your new People table. Once you have done that, you can use a Roll Up field in the People table to sum all of the values in your Quantity field (in the original table) that are linked to that person.

As an aside, make sure that you are giving your fields the correct type. I’m pretty sure your Quantity field should be a Number field, rather than a Single Line Text field.

[Alex_Aguilar]

Thank you @David_Skinner thats a step in the right direction but it rolls up the total QTYs to just the name, instead of the name/date. Many of the names will have multiple different dates and different QTY’s to each date, any suggestions?

[Jeremy_Oglesby]

I’d suggest you try using the “Group” feature at the top to see if that will suit your needs. In your “Events” table, Group it first by “Person”, then by “Date”.

You’ll end up with sections that have summary bars on them. You’ll have a top-level section for “AJ”, and a summary bar that shows AJ’s total Quantity for all dates. But then inside the AJ section, you have sub-sections, one for each “Date” (which presumably represents an event). Each “Date” section will also have its own summary bar, showing AJ’s total Quantity for just that Date.

[Alex_Aguilar]

Thanks for the reply, unfortunately i do have this view but due to constant movement around this table i need these QTY’s inputted into a different table, so I can run different formulas off them with a different set of views and purposes. I’m trying to figure a way around inputting this unique data manually into the separate table by making it automatic if I have linked records, a list table, etc

[Alex_Aguilar]

All I really need is the circled number in this pic inputted automatically into its designated row on the next table.

[Jeremy_Oglesby]

Another option that comes to mind is to build a sort of “search” function into your “People” table.

In your “People” table, create a Date field that matches the format of the the Date field in your “Events” table. You will enter a Date in that field to query the “Quantity” for a person on that Date.

Next, in your “Events” table, create a Rollup field, let’s call it “Quantity Query”, with this formula:

IF(values = {Date}, {Quantity})

Back in your “People” table, create a Rollup field that looks at “Events” >> “Quantity Query” and uses the SUM(values) function.

That will allow you to enter a date next to a person’s name to query that person’s total quantity for that date.