.png){kind=logo}
How to Add a Total Score Column in a Clay Table
In a clay table, I have 2 rows of the same company name, with a score each, I want to add another column with the total score for that account, how can i do this?
In a clay table, I have 2 rows of the same company name, with a score each, I want to add another column with the total score for that account, how can i do this?
You can use the Lookup Multiple Rows integration to sum scores for the same company. This scans your table for matching company names and can count or aggregate values. Alternatively, create a Formula column: 1. Add a new column and set type to Formula 2. Use the Formula Generator to create a formula that sums scores for matching company names 3. For number-based scoring, use formulas to calculate total scores by adding points from multiple rows You can also use AI formulas - add a formula column and prompt the AI to sum scores for each company name. AI will generate the appropriate code automatically.
Can you tell me more about how I can use lookup multiple rows please
To use Lookup Multiple Rows to sum scores for the same company: 1. Add a new column and select "Lookup Multiple Rows in Other Table" 2. Configure the lookup: • Table to Search: Select your current table (same table) • Target Column: Choose the company name column • Filter Operator: Set to "equals" or "contains" • Row Value: Reference the company name from the current row 3. Process the results: The lookup will return all matching rows for that company. You'll then need to extract and sum the score values from those returned rows 4. Sum the scores: Use a formula column to add up all the score values from the lookup results This scans your table for matching company names and retrieves all rows with the same company, allowing you to aggregate their scores into a total.
But this would only work when I have 2 rows. If I have 100 rows, with different company names, but 2 rows per company, and for each of those companies I want to total the score, then how would I make this work?
For 100 rows with multiple companies appearing twice each, use Lookup Multiple Rows to automatically calculate totals for each company: 1. Add a new column and select "Lookup Multiple Rows in Other Table" 2. Configure the lookup: • Table to Search: Your current table • Target Column: Company name column • Filter: "equals" • Row Value: Reference the company name from current row 3. Sum the results: The lookup returns all matching rows for each company. Use a Formula column to extract and sum the score values from those returned rows Alternative approach: Use AI formulas - add a formula column and prompt the AI to "sum all scores for each company name in this table." The AI will generate appropriate code to calculate totals automatically Both methods work regardless of how many companies or rows you have - they'll automatically total scores for each unique company name.
To make this clearer, I have one table which has an accounts list. In another table, I have a list of companies that have a score assigned to those companies based on two things:
Visiting our website for the first time
Visiting our website again
For visiting our website for the first time, it is captured in one row, and they get 1 point. For visiting our website again, it is captured in another row, and they get 1 point again. So depending on the first and/or multiple visits, there may be multiple rows of the same company. Now I would like to add up the scores of these rows of the same company, and write it to the first table which has a single row per company. What would be the best way to do that?
Use Lookup Multiple Rows in Other Table in your accounts table to sum scores from the scoring table: 1. In your accounts table, add a new column and select "Lookup Multiple Rows in Other Table" 2. Configure the lookup: • Table to Search: Your scoring table • Target Column: Company name column • Filter: "equals" • Row Value: Reference the company name from current row 3. This returns all matching rows for each company 4. Add a Formula column to sum the score values from the lookup results Alternatively, use write to table to aggregate the data: ** From your scoring table, use write to table to send summed scores to your accounts table ** This transfers the aggregated totals directly to the single-row-per-company table
Can you please tell me more on Row Value: Reference the company name from current row Right now the scoring table is a test, so in that I only have 1 company with 2 rows, one visiting the first time, and the second row for the same company but for a new visit. So what would Row Value be?
For the Row Value field, you need to reference the column containing the company name in your current table (accounts table). Click on the Row Value field and select the company name column from your accounts table. This tells the lookup to match against whatever company name appears in each row. For example, if your accounts table has a column called "Company Name", you'd select that column as your Row Value. The lookup will then find all rows in your scoring table where the company name matches the company name in the current row of your accounts table. This creates a dynamic reference that works for all companies in your table automatically.
On another note, I have a people list that comes from my accounts list. For each company, I have also assigned scores to leads. now I'd like to total it up and have the total of leads for a company in the respective row in the accounts table. How can I do this?
Use Lookup Multiple Rows in Other Table in your accounts table: 1. Add a new column and select "Lookup Multiple Rows in Other Table" 2. Configure: • Table to Search: Your people/leads table • Target Column: Company name column • Filter: "equals" • Row Value: Reference the company name column from your accounts table 3. Add a Formula column to sum the score values from the lookup results This will automatically total all lead scores for each company and display the sum in your accounts table.
I'm not able to get the formula to total the total engagement score for each person in the row for a company. Can you please help?
After your Lookup Multiple Rows returns the matching people data, create a Formula column to sum the engagement scores: 1. Add a new Formula column in your accounts table 2. Use the AI Formula Generator and prompt it to "sum all engagement scores from the lookup results" 3. Reference the lookup column containing the people data using "/" 4. The AI will generate code to extract and total the engagement scores automatically Alternatively, use AI formulas directly - prompt the AI to "calculate total engagement scores for all people from this company" and it will generate the appropriate code to sum the values from your lookup results.