How do I sum a customer name list of transactions into separate groups for each consultant on Google Sheet?

1
votes

I have 3 sheets I work with.

Transaction Sheet - This file contains a long list of customer's names and individual transaction amounts each line.

Customer List Sheet - This file is a list of customers that has been grouped to each consultant's.

Summary Sheet - This file sums each Consultant's group of customers' transactions to their group.

I'm not exactly sure how to put together a formula that could skim through the Customer List and automatically sum up the individual transaction amounts to the Consultant's group.

"If "Name" = "Name from File", then send amount to group.

Example: https://docs.google.com/spreadsheets/d/1VRt29wJkkTSdBFO1sitGdd70z7ALZZLQQ1nOpvdOVDw/edit?usp=sharing

1
regexgoogle-sheetsfiltersumgoogle-sheets-formula
share a copy of your sheet with example of desired output - player0
Hi, hope this is a proper example. docs.google.com/spreadsheets/d/… - user12977360

1 Answers

1
votes

paste in B3 and drag to the right:

=SUM(FILTER(Transactions!$B:$B, REGEXMATCH(Transactions!$A:$A, 
 TEXTJOIN("|", 1, INDIRECT("Customer List!"&ADDRESS(2, 
 MATCH(B2, 'Customer List'!1:1, 0), 4)&":"&
 CHAR(64+MATCH(B2, 'Customer List'!1:1, 0)))))))

0