Find every occurrence of a specified value and return the sum product of A1 from each row where a value was found.

[Epoch18]

Hi there.

I've been banging my head on this for too long, and everything I search up never gets me to the right place. So here is the scenario, and what I am looking for.

Data:
My data is given to me as converted from JSON, which means that the headers are never in the same place, except for the first few. Luckily the value I want to sum is always in column A.
Example:

	A	B	C	D	E	F	G	H	I	J
1	Value	Account	Name1	Name2	Name3	Name4	Name5	Name6	Name7	Name8
2	150000	Acme	Kyle	Sam	Sarah	Tim	Melissa	Brian	Laura	Karl
3	250000	Walmart	Sarah	Kyle	Kenny
4	50000	Target	Sarah	Tim
5	325000	Kmart	Melissa	George	Kenny	Laura

<tbody>
</tbody>

Parameters:
I have a list of all of my employee names on a separate tab. These should be my search parameters for each row in my data.
Example:
Brian
George
Karl
Kenny
Kyle
Laura
Melissa
Sam
Sarah
Tim


Results:
Next to each of my employee names, I want to see the total sum of each value is A1 for every row that each employee name is present. As you can see from my data example, the names are almost never in the same column. So, if "Laura" exists anywhere within rows 2 and 5, then the number next to her name should be "475000" (the sum of A2 and A5).

I would greatly appreciate any ideas.

Thank you!

 

[Grahhhhh]

Ready for some ugly, dirty looking but gets the job done code?

=SUM(IF(COUNTIF(B2:J2,"Laura") > 0,A2,0),IF(COUNTIF(B3:J3,"Laura") > 0,A3,0),IF(COUNTIF(B4:J4,"Laura") > 0,A4,0),IF(COUNTIF(B5:J5,"Laura") > 0,A5,0))

Basically each "number" is:

IF(COUNTIF(B2:J2,"Laura") > 0,A2,0)

Just change the range, the number to grab, and the name to search for to suit your needs.

 

[Marcelo Branco]

Assuming data in Sheet1, maybe...

B1 copied down
=SUMPRODUCT((Sheet1!C$2:J$5=A1)*(Sheet1!$A$2:$A$5))

Hope this helps

M.

 

[Epoch18]

This could get very messy. Each data sheet is at least 200 rows and there's a new one each month. I don't think this is a viable solution, though I do appreciate your response.

 

[Epoch18]

This doesn't specify any employee names. Thank you though.

Assuming data in Sheet1, maybe...

B1 copied down
=SUMPRODUCT((Sheet1!C$2:J$5=A1)*(Sheet1!$A$2:$A$5))

Hope this helps

M.

 

[Rick Rothstein]

Assuming data in Sheet1, maybe...

B1 copied down
=SUMPRODUCT((Sheet1!C$2:J$5=A1)*(Sheet1!$A$2:$A$5))

This doesn't specify any employee names. Thank you though.

It doesn't have to specify any names... you are assumed to have done that already. Marcelo assumed your data was on Sheet1 and that on a different sheet, starting in cell A1 on that different sheet, is the list of names you showed in your original message. So, Marcelo is instructing you to place his formula in cell B1 on that different sheet and copy it down to the end of your list of names.

 

[Marcelo Branco]

This doesn't specify any employee names. Thank you though.

It doesn't have to specify any names... you are assumed to have done that already. Marcelo assumed your data was on Sheet1 and that on a different sheet, starting in cell A1 on that different sheet, is the list of names you showed in your original message. So, Marcelo is instructing you to place his formula in cell B1 on that different sheet and copy it down to the end of your list of names.

That's it! Thank you, Rick.

M.

 

[Epoch18]

It doesn't have to specify any names... you are assumed to have done that already. Marcelo assumed your data was on Sheet1 and that on a different sheet, starting in cell A1 on that different sheet, is the list of names you showed in your original message. So, Marcelo is instructing you to place his formula in cell B1 on that different sheet and copy it down to the end of your list of names.

You're right. I didn't really see what the formula was doing. But I tried it out and it appears to be working.

Thank you, both!

 

[Marcelo Branco]

You're right. I didn't really see what the formula was doing. But I tried it out and it appears to be working.

Thank you, both!

You are welcome. Glad we help.

M.