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...[TABLE="width: 695"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Operator Name[/TD]
[TD]Shift[/TD]
[TD]Machine Number[/TD]
[TD]Machine Speed[/TD]
[TD]Up-Time[/TD]
[TD]Actual Goal [/TD]
[TD]Up time Goal[/TD]
[TD]Produced[/TD]
[/TR]
[TR]
[TD]3/2/2015[/TD]
[TD]Adriana Silva[/TD]
[TD]1st[/TD]
[TD]XX-01[/TD]
[TD]20[/TD]
[TD]320[/TD]
[TD]7,680[/TD]
[TD]6,400[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/2/2015[/TD]
[TD]Andrew Gonzales[/TD]
[TD]1st[/TD]
[TD]XX-02[/TD]
[TD]20[/TD]
[TD]320[/TD]
[TD]7,680[/TD]
[TD]6,400[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/2/2015[/TD]
[TD]Augustina Flores[/TD]
[TD]1st[/TD]
[TD]XX-03[/TD]
[TD]20[/TD]
[TD]320[/TD]
[TD]7,680[/TD]
[TD]6,400[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/3/2015[/TD]
[TD]Adriana Silva[/TD]
[TD]1st[/TD]
[TD]XX-01[/TD]
[TD]20[/TD]
[TD]120[/TD]
[TD]7,680[/TD]
[TD]2,400[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/3/2015[/TD]
[TD]Andrew Gonzales[/TD]
[TD]1st[/TD]
[TD]XX-02[/TD]
[TD]20[/TD]
[TD]120[/TD]
[TD]7,680[/TD]
[TD]2,400[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/3/2015[/TD]
[TD]Augustina Flores[/TD]
[TD]1st[/TD]
[TD]XX-03[/TD]
[TD]20[/TD]
[TD]120[/TD]
[TD]7,680[/TD]
[TD]2,400[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]



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]

[TABLE="width: 766"]
<colgroup><col><col><col span="5"><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Operator Name[/TD]
[TD]Shift[/TD]
[TD]Machine Number[/TD]
[TD]Machine Speed[/TD]
[TD]Up-Time[/TD]
[TD]Actual Goal[/TD]
[TD]Up time Goal[/TD]
[TD]Produced[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/02/2015[/TD]
[TD]Adriana Silva[/TD]
[TD]1st[/TD]
[TD]XX-01[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]7,680[/TD]
[TD="align: right"]6,400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/02/2015[/TD]
[TD]Andrew Gonzales[/TD]
[TD]1st[/TD]
[TD]XX-02[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]7,680[/TD]
[TD="align: right"]6,400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/02/2015[/TD]
[TD]Augustina Flores[/TD]
[TD]1st[/TD]
[TD]XX-03[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]7,680[/TD]
[TD="align: right"]6,400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/03/2015[/TD]
[TD]Adriana Silva[/TD]
[TD]1st[/TD]
[TD]XX-01[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]7,680[/TD]
[TD="align: right"]2,400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/03/2015[/TD]
[TD]Andrew Gonzales[/TD]
[TD]1st[/TD]
[TD]XX-02[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]7,680[/TD]
[TD="align: right"]2,400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/03/2015[/TD]
[TD]Augustina Flores[/TD]
[TD]1st[/TD]
[TD]XX-03[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]7,680[/TD]
[TD="align: right"]2,400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Up-time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Adriana Silva[/TD]
[TD="align: right"]440[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]formula is[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=SUMPRODUCT(($B$2:$B$7=B20)*(F2:F7))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]name required is in b20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[TABLE="width: 1112"]
<tbody>[TR]
[TD]Operator Name[/TD]
[TD]Shift[/TD]
[TD]Machine Number[/TD]
[TD]Machine Speed[/TD]
[TD]Up-Time[/TD]
[TD]Actual Goal[/TD]
[TD]Up time Goal[/TD]
[TD]Produced[/TD]
[/TR]
[TR]
[TD]Adriana Silva[/TD]
[TD]1st[/TD]
[TD]XX-04[/TD]
[TD]38[/TD]
[TD]300[/TD]
[TD]14,592[/TD]
[TD]11,400[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]Andrew Gonzales[/TD]
[TD]1st[/TD]
[TD]XX-01[/TD]
[TD]27[/TD]
[TD]300[/TD]
[TD]10,368[/TD]
[TD]8,100[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]Augustina Flores[/TD]
[TD]1st[/TD]
[TD]XX-01[/TD]
[TD]27[/TD]
[TD]300[/TD]
[TD]10,368[/TD]
[TD]8,100[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]Adriana Silva[/TD]
[TD]1st[/TD]
[TD]XX-04[/TD]
[TD]38[/TD]
[TD]300[/TD]
[TD]14,592[/TD]
[TD]11,400[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]Andrew Gonzales[/TD]
[TD]1st[/TD]
[TD]XX-01[/TD]
[TD]27[/TD]
[TD]300[/TD]
[TD]10,368[/TD]
[TD]8,100[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]Augustina Flores[/TD]
[TD]1st[/TD]
[TD]XX-01[/TD]
[TD]27[/TD]
[TD]300[/TD]
[TD]10,368[/TD]
[TD]8,100[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]Antonia Avalos[/TD]
[TD]1st[/TD]
[TD]XX-01[/TD]
[TD]27[/TD]
[TD]300[/TD]
[TD]10,368[/TD]
[TD]8,100[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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...

[TABLE="width: 717"]
<tbody>[TR]
[TD]Weekly Totals[/TD]
[TD]Operator Name[/TD]
[TD]Up Time Total[/TD]
[TD]Total Actual Goal[/TD]
[TD]Total Up Time Goal[/TD]
[TD]Total Produced[/TD]
[/TR]
[TR]
[TD]1st[/TD]
[TD]Adriana Silva[/TD]
[TD]600[/TD]
[TD]#N/A[/TD]
[TD]#VALUE![/TD]
[TD]24000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Andrew Gonzales[/TD]
[TD]600[/TD]
[TD]#N/A[/TD]
[TD]#VALUE![/TD]
[TD]24000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Angelica Ortega[/TD]
[TD]0[/TD]
[TD]#N/A[/TD]
[TD]#VALUE![/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

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.