I have a google doc with 4 main cells that are updated based on entries from a form spanning A1 - D1. (Timestamp, Technician's Name, Work Order, Flat Rate Hours) Responses are placed A2 - D2 through infinity. E2 - I2 is the technician's names. On E3 - I3 is planned to be the total Flat Rate hours for each respective technician. Cannot seem to find a formula that will search the values of B2 and beyond for the tech's name and then read the data on D2 and beyond for the hours belonging to that particular technician and then print that value (rather update it to the appropriate cell between E3 - I3 respectively.

enter image description here

2 Answers

1
Community On Best Solutions

One approach is to use ARRAYFORMULA, and create an array using { val1, val2, etc }. Then you can use FILTER to handle the logic (if Col B = technician's name, return value in Col D), and SUM to add up all the values returned by FILTER. IFERROR just ignores the error that ARRAYFORMULA returns when a technician hasn't billed anything yet.

Try placing the following formula in cell E3.

=iferror(arrayformula({
sum(filter(D2:D,B2:B=E2)),
sum(filter(D2:D,B2:B=F2)),
sum(filter(D2:D,B2:B=G2)),
sum(filter(D2:D,B2:B=H2)),
sum(filter(D2:D,B2:B=I2))
}), "")
1
player0 On

proper way would be:

=ARRAYFORMULA(IFERROR(HLOOKUP(E2:2, TRANSPOSE(QUERY(B2:D, 
 "select B,sum(D) where B !='' group by B label sum(D)''")), 2, 0)))

0