Options for searching through data based on Name, and returning any and all values from a column

[JST013]

Hello mrExcel users!

I am JST013 and I am stumped...

Date	Operator Name	Shift	Machine Number	Machine Speed	Up-Time	Actual Goal	Up time Goal	Produced
3/2/2015	Adriana Silva	1st	XX-01	20	320	7,680	6,400
3/2/2015	Andrew Gonzales	1st	XX-02	20	320	7,680	6,400
3/2/2015	Augustina Flores	1st	XX-03	20	320	7,680	6,400
3/3/2015	Adriana Silva	1st	XX-01	20	120	7,680	2,400
3/3/2015	Andrew Gonzales	1st	XX-02	20	120	7,680	2,400
3/3/2015	Augustina Flores	1st	XX-03	20	120	7,680	2,400

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

This is what I am currently dealing with..

Date, operator, shift, machine number, machine speed, up time , goal, uptime goal and produced are my columns..

If i wanted to make a formula to seach through column B (operators) and return a value found in any of the other columns, Up time for example, and sum the results...how could I do this?

so lets say my operator is Adriana Silva, and I want to know the total of her up-time...

How can I total just her uptime?

Thank you so much!

 

[oldbrewer]

Date	Operator Name	Shift	Machine Number	Machine Speed	Up-Time	Actual Goal	Up time Goal	Produced
03/02/2015	Adriana Silva	1st	XX-01	20	320	7,680	6,400
03/02/2015	Andrew Gonzales	1st	XX-02	20	320	7,680	6,400
03/02/2015	Augustina Flores	1st	XX-03	20	320	7,680	6,400
03/03/2015	Adriana Silva	1st	XX-01	20	120	7,680	2,400
03/03/2015	Andrew Gonzales	1st	XX-02	20	120	7,680	2,400
03/03/2015	Augustina Flores	1st	XX-03	20	120	7,680	2,400
		Up-time
	Adriana Silva	440
		formula is
		=SUMPRODUCT(($B$2:$B$7=B20)*(F2:F7))
		name required is in b20

<colgroup><col><col><col span="5"><col><col span="2"></colgroup><tbody>
</tbody>

 

[Darren Bartrup]

Hi and welcome to the board.

Look at the SUMIF, SUMIFS, COUNTIF and COUNTIFS formula.

If your data set is in cells A1:I7 your question can be answered with =SUMIF($B$2:$B$7,"Adriana Silva",$F$2:$F$7) - ​Adriana Silva can be replaced with a cell reference.

 

[JST013]

Thank you both! I tried both solutions and both worked perfectly!

I'm going to play with the SUMIF & COUNTIF options as well!

Thanks again

 

[JST013]

Operator Name	Shift	Machine Number	Machine Speed	Up-Time	Actual Goal	Up time Goal	Produced
Adriana Silva	1st	XX-04	38	300	14,592	11,400	12,000
Andrew Gonzales	1st	XX-01	27	300	10,368	8,100	12,000
Augustina Flores	1st	XX-01	27	300	10,368	8,100	12,000
Adriana Silva	1st	XX-04	38	300	14,592	11,400	12,000
Andrew Gonzales	1st	XX-01	27	300	10,368	8,100	12,000
Augustina Flores	1st	XX-01	27	300	10,368	8,100	12,000
Antonia Avalos	1st	XX-01	27	300	10,368	8,100	12,000
					#N/A	#VALUE!
					#N/A	#VALUE!
					#N/A	#VALUE!
					#N/A	#VALUE!

<tbody>
</tbody>

So another challenge I have run into...My table here is set up for data entry..but
I cannot look at my totals until I fully fill in my data entry table...is there a way to avoid this? The option I went with was the =SUMPRODUCT option...

I'm tracking the totals in a separate table that looks something like this...

Weekly Totals	Operator Name	Up Time Total	Total Actual Goal	Total Up Time Goal	Total Produced
1st	Adriana Silva	600	#N/A	#VALUE!	24000
	Andrew Gonzales	600	#N/A	#VALUE!	24000
	Angelica Ortega	0	#N/A	#VALUE!	0

<tbody>
</tbody>

As you can see my values are not updating due to the table not being completely filled in... in the totals table column D formulas are as follows

=SUMPRODUCT((A2:A20=B40)*F2:F20)

and column E

=SUMPRODUCT((A2:A20=B40)*G2:G20)

what am i doing wrong? does all of this make sense?

Thank you again!

 

[JST013]

Side Note: I tried the SUMIF( A2:A20, "Adriana Silva", F2:F20) and that one worked well, but I need to make the criteria a variable, as I'm not confident the operator column will always be the same

Thank yoU!

 

[JST013]

Oh wow I figured it out I think...

instead of Adriana Silva, I used the cell #...haha which you already told me I could use! You guys are great!

Thanks again!

 

[Darren Bartrup]

Happy to help.