New answers tagged


Is it at all possible to use column headers like this? Yes, it is possible. First, you need to use MATCH to get the column number of the column whose value matches 'Name'. Then you need to use ADDRESS to get the cell reference. Finally, you need to use SUBSTITUTE to remove the row number from the cell reference. =QUERY(Sheet1!A1:C,"SELECT A, B, C where ...


The main goal is to make sum ArrayFormula. The original brilliant solution was provided here. And your formula is: =ArrayFormula( IF($Q2:Q="No", SUMIF(IF(COLUMN(J2:P2),ROW(A2:A)),ROW(A2:A),J2:P), IF($Q2:Q="Yes", SUMIF(IF(COLUMN(R2:X2),ROW(A2:A)),ROW(A2:A),R2:X), IF($Q2:Q="",""))))


The way I usually tend to do my arrayformulas, and actually any formula that requires action based on a value in another cell, is i start them off with an ISTEXT statement, and then just a simple comma in place of the else portion: ={"Start Time";arrayformula(IF(ISTEXT(B2:B),text(C2:C+vlookup(D2:D,Administrative!$A$1:$C$10,2,false),"mm-dd-yyyy ...


If column B has week number and columns E,F,G,H,I,J have quantities for which you need weekly totals, then query returns the relevant totals: =query(B:J, "select B, sum(E), sum(F), sum(G), sum(H), sum(I), sum(J) group by B") Alternatively, you can create a pivot table report.

Top 50 recent answers are included