Need Help Writing Google Sheets formula

0
votes

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
google-sheetsgoogle-sheets-formula
what im trying to do is this IF B2 = "Ben" ; THAN "E3=C2" ; IF B2 DOES NOT = "Ben" Continue B3 etc... until the formula has exceeded all values submitted in the B Column, then it adds up all the values of C2 - C999 as they Correspond to matching values of "Ben" Found in the B Column and Display's the total value on E3 being the sum of the values found in C2-C999 – David Desilets
Why not tag google sheets - as you are using google sheets and not excel. – Solar Mike

2 Answers

1
votes

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
votes

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