Search code examples
arraysif-statementgoogle-sheetsvlookupgoogle-query-language

How to get total number of row entry that is grouped by rows on google sheets

I have a table of data that is formatted like this

enter image description here

and i would like to extrapolate how many ASSIGNMENTS, PROJECT, and PERMITS total on all the engineers.

Im having problem because this does not really deal with numbers so no mathematical formula i cant think of will work.

Here is a sheet link of the image above.

Here is my idea of doing it but i have no idea how to code this into a formula on excel.

The formula will look for the key word, assignment, project, or permit and will then check if the next 2 column have contents in them if so then it is a plus 1, if the cell below the keyword(assignment,proect,or permit) is empty check if there is contents to 2 column beside the empty cell if empty then stop, if there is plus 1, if a plus 1 check again for an empty cell below that one rinse and repeat.

Solution

  • try:

    =ARRAYFORMULA(QUERY(REGEXREPLACE(IF(B3:B="",, VLOOKUP(ROW(A3:A), IF(A3:A<>"", {ROW(A3:A), A3:A}), 2, 1)), "s$", ), 
 "select Col1,count(Col1) where not Col1 matches '^Enginee.+' and Col1 is not null group by Col1 label count(Col1)''"))

    enter image description here

    update:

    =ARRAYFORMULA(QUERY(REGEXREPLACE(IF(Sheet1!B3:B="",, VLOOKUP(ROW(Sheet1!A3:A), IF(Sheet1!A3:A<>"", {ROW(Sheet1!A3:A), Sheet1!A3:A}), 2, 1)), "s$", ), "select Col1,count(Col1) where Col1 matches 'Assignement' and Col1 is not null group by Col1 label count(Col1)''"))