Not sure if I need to use CountIFS, SumIFS, Match. Need to sum rows with Match ifs?

[xeroplasmic]

Hello all,

First off, I am trying to make a spreadsheet that has a few different components to it - primarily, we are working with 3 seperate sheets in the workbook. I am not sure if I can turn my nameslist sheet into an array of names, and somehow run =sumifs(match "formula") or how I would go about doing that. But here is the rest of what I have and what i'm looking for.

this is what my sheets look like:

i.imgur.com/kZw0d5h.png - dataset

i.imgur.com/PjZQfQj.png - Reports

i.imgur.com/MUjrFRO.png - Nameslist

I will break what I need to do down into parts -

If we look at the reports tab, I need to be able to 1. Take only Rows in the range A2:A (the column that shows the trade location) that say Transunion

2. Be able to take Only Traders name's which are in my sheet labeled as nameslist

3. Be able to get the sum from the Column which says Amount in the range C2:C

4. I need to return the sum/value the Total Amount of Transunion Trades which have been traded by anyone in my nameslist, and exclude anyone that is not in my nameslist into sheet dataset - D:3.

The current formula, only returns the count of times that the traders name appears if it is a transunion trade in column A.

Here is what I have ( I know it's messy but it's all i could come up with) :

=COUNTIFS(Reports!$A:$A, "=" & $A3, Reports!$P:$P, "=" & Nameslist!$A1) + COUNTIFS(Reports!$A:$A, "=" & $A3, Reports!$P:$P, "=" & Nameslist!$A2) + COUNTIFS(Reports!$A:$A, "=" & $A3, Reports!$P:$P, "=" & Nameslist!$A3) + COUNTIFS(Reports!$A:$A, "=" & $A3, Reports!$P:$P, "=" & Nameslist!$A4)+ COUNTIFS(Reports!$A:$A, "=" & $A3, Reports!$P:$P, "=" & Nameslist!$A5)+ COUNTIFS(Reports!$A:$A, "=" & $A3, Reports!$P:$P, "=" & Nameslist!$A6)+ COUNTIFS(Reports!$A:$A, "=" & $A3, Reports!$P:$P, "=" & Nameslist!$A7)+ COUNTIFS(Reports!$A:$A, "=" & $A3, Reports!$P:$P, "=" & Nameslist!$A8)

Can anyone please help me with this, I have my report due by friday...

if requested, I would be more than happy to share my workbook as it is with actual values and names changed/removed


First Image is the Dataset
Second Image is the Reports
Third is the Nameslist