Google-sheets – Collecting a sum of data until another name is mentioned

google sheets

Every day I get a spreadsheet of info for all members of the company. Its broken down by employee, then the store that the employee worked in. The problem is, the cells with the employee name is merged so I can't just sumif the name is right. So I need to sumif for the name, then the empty cells below it until there's another name.

Example:

Name 1  Store 1   4
        Store 2   5
        Store 3   2
Name 2  Store 1   3
        Store 2   4

So what I'm trying to get is the total for Name 1, which in this example would be 11.

Best Answer

Use this formula

=query({query(ArrayFormula(vlookup(ROW(1:5),{IF(LEN(A1:A5)>0,ROW(1:5),""),A1:A5},2)),"select Col1"),query(B1:C5,"select B, C")}, "select Col1, sum(Col3) group by Col1 label Sum(Col3) ''")

The answer to this question comprises 3 elements.

Populate the empty names in Column A
- In An arrayformula to find the previous non-empty cell in another column, @Rubén provided an answer (with detailed explanation) to fill the missing names.
Create a query to combine the newly populated column A with the existing Column B and Column C
In the query, modify the select clause to display the sum of Column C grouped by Name

Related Solutions

Google-sheets – How to create a Google Spreadsheets whose name and cells auto-complete with cells completed on another sheet

I think you should be looking in the direction of using a single form to more or less accomplish both tasks. In other words use the form to populate both the Gantt chart AND the new project sheet.

Google-sheets – Create a 7 day running total and running average using ARRAYFORMULA

This can be done by using the running total as an intermediate result (you can put in some column further on the right and/or make it hidden, if you don't want to see it).

I'll use column C for the cumulative total and column D for 7-day total; finding the average is simple division.

The formula for cumulative total is found here. I'll use it in this form

C2 =ARRAYFORMULA(IF(LEN(A2:A), SUMIF(ROW(B2:B),"<="&ROW(B2:B), B2:B), ""))

The conditional statement about LEN(A2:A) prevents output in rows where no data is present.

And here is the computation of 7-day running total:

D2 =ARRAYFORMULA(IF(LEN(A2:A), C2:C-IFERROR(VLOOKUP(A2:A-7, A2:C, 3), 0), ""))

The key player here is the VLOOKUP function which find the latest date that is less than or equal to "a week ago", and returns the cumulative total for it. This represents the total of "old" entries, which should be subtracted from the current total so that only the entries from the last 7 days are counted.

The IFERROR statement handles the case when there is nothing to subtract, i.e., all preceding dates are within the 7-day window.

Here is sample output to verify correctness:

+-----------+------+---------------+-------------+
|   Date    | Data | Running Total | 7-day total |
+-----------+------+---------------+-------------+
| 1/4/2015  |    5 |             5 |           5 |
| 1/5/2015  |    3 |             8 |           8 |
| 1/9/2015  |    1 |             9 |           9 |
| 1/11/2015 |    9 |            18 |          13 |
| 1/12/2015 |    4 |            22 |          14 |
| 1/13/2015 |    2 |            24 |          16 |
| 1/27/2015 |   64 |            88 |          64 |
+-----------+------+---------------+-------------+

Best Answer

Related Solutions

Google-sheets – How to create a Google Spreadsheets whose name and cells auto-complete with cells completed on another sheet

Google-sheets – Create a 7 day running total and running average using ARRAYFORMULA

Related Topic