I have a list of email addresses in one sheet (first column).

I have a list of transactions in another sheet with emails and sale amounts.

I am looking for a formula that adds up all the transaction $ sales for any transactions made by the people (emails) in the first sheet.

Any help would be much appreciated!

CodePudding user response：

If I understood your question correctly! Try this on the sheet, where you only have emails and wanted to get sum of sales amount

=sumifs(range_whichHasTransaction , range_of_Email_inThat_TransactionsTable , Cell_Reference_ofEmail_forWhich_you_want_sum_the_Transaction_Amount)

it will Look something like this:-

sumifs(TransactionSheet!B:B,TransactionSheet!A:A,Emails!A2)

Reference

SUMIFS

CodePudding user response：

Your sample data is very limited (only one row matching one person, in fact). But the following formula should work for you. Place it in a new sheet, in cell A1:

=ArrayFormula(IFERROR({"Name","Total"; QUERY(FILTER(Transactions!A2:D,NOT(ISERROR(VLOOKUP(Transactions!A2:A,Tags!A:A,1,FALSE)))),"Select Col2, SUM(Col4) GROUP BY Col2 ORDER BY Col2 LABEL SUM(Col4) '' ")},"No Data"))

This one formula will produce the headers (which you can change within the formula itself as desired) and all results.

FILTER filters in only 'Transactions' data (names through amounts) where the emails are found in the 'Tags' sheet.

Then QUERY returns the name and totals for each match in alphabetical order by name.

If there are no matches, "No Data" will show instead.