Reputation: 20761

How to sum conditionally every column of a multiples rows that match a criteria

I'm trying to SUM multiple columns of every rows that match a criteria.

I've a table with the time planned of multiple people over several tasks over the years:

In another table in the same file(let's admit in the same sheet), I'm trying to say, for every months, what is the time planned for every task, no matter the user, for the current month and the following ones.

Typically, for the cell for "January - Task 1", I would like to have 2+4+4+5+2+5 = 22 But for the same task, in July, I only expect to have 4+5+2+5 = 16, in august only 5+2+5=12, ...

When several people worked on the same task, their time should be also summed up.

I could, manually, for each task, summing the July->December for all the rows I know are assigned to this task, but there is a lot of tasks, I might add a task to some people, ... I would like, for each task to automatically search for all the assignement.

I tried to assemble some "SUMIF", but I didn't managed to have something working. I could see some way to make it work, by adding a column after each month that provide the sum of the next ones, but in my real case, I've 36 months, and that makes a lot of columns and noise.

Upvotes: 1

Answers (5)

nkalvi

Reputation: 2614

SCANning the monthly times for each task over the years , DROPping each column:

Using structured reference

=LET(
    people, TEXTSPLIT("Henry,Francis,Georges", ","),
    comment_1, "Assuming tasks <> people",
    tasks, SORT(
        FILTER(
            Table1[Column1],
            (LEN(TRIM(Table1[Column1])) > 0) * NOT(ISNUMBER(XMATCH(Table1[Column1], people)))
        )
    ),
    current_and_remaining, DROP(
        REDUCE(
            "",
            tasks,
            LAMBDA(acc, cur_task,
                VSTACK(
                    acc,
                    SCAN(
                        0,
                        SEQUENCE(, COLUMNS(Table1) - 1),
                        LAMBDA(acc, cur, SUM(DROP(FILTER(Table1, Table1[Column1] = cur_task), , cur)))
                    )
                )
            )
        ),
        1
    ),
    VSTACK(
        HSTACK("Current and remaining time", DROP(Table1[#Headers], , 1)),
        HSTACK(tasks, current_and_remaining)
    )
)

Just ranges

=LET(
    people, TEXTSPLIT("Henry,Francis,Georges", ","),
    people_tasks, $A$2:$A$15,
    header, $A$1:$M$1,
    data, $B$2:$M$15,
    comment_1, "Assuming tasks <> people",
    tasks, SORT(
        FILTER(
            people_tasks,
            (LEN(TRIM(people_tasks)) > 0) *
                NOT(ISNUMBER(XMATCH(people_tasks, people)))
        )
    ),
    times_for_current_and_remaining, DROP(
        REDUCE(
            "",
            tasks,
            LAMBDA(acc, cur_task,
                VSTACK(
                    acc,
                    TRANSPOSE(
                        SCAN(
                            0,
                            SEQUENCE(COLUMNS(data)),
                            LAMBDA(acc, cur,
                                SUM(
                                    DROP(
                                        FILTER(
                                            data,
                                            people_tasks =
                                                cur_task
                                        ),
                                        ,
                                        cur - 1
                                    )
                                )
                            )
                        )
                    )
                )
            )
        ),
        1
    ),
    VSTACK(
        header,
        HSTACK(tasks, times_for_current_and_remaining)
    )
)

Upvotes: 0

DjC

Reputation: 1312

Instead of outputting the results to a structured table via calculated columns, or having to drag down and across with relative cell references, you could try the following single-cell dynamic array formula using MAP to loop through 2 arrays at once:

=LET(
    task_list, SORT(UNIQUE(TOCOL(IFS(DROP(Table1,, 1)<>"", Table1[Column1]), 2))),
    cols, COLUMNS(Table1),
    col_nums, SEQUENCE(, cols - 1, 2),
    results, MAP(IF(col_nums, task_list), IF(SEQUENCE(ROWS(task_list)), col_nums),
        LAMBDA(task,num,
            SUM((Table1[Column1]=task) * INDEX(Table1,, num):INDEX(Table1,, cols))
        )
    ),
    HSTACK(
        VSTACK("Remaining time planned", task_list),
        VSTACK(DROP(Table1[#Headers],, 1), results)
    )
)

Alternatively, SUM(FILTER(FILTER(...))) can be used with the same method:

=LET(
    values, DROP(Table1,, 1),
    task_list, SORT(UNIQUE(TOCOL(IFS(values<>"", Table1[Column1]), 2))),
    col_nums, SEQUENCE(, COLUMNS(values)),
    results, MAP(IF(col_nums, task_list), IF(SEQUENCE(ROWS(task_list)), col_nums),
        LAMBDA(task,num,
            SUM(FILTER(FILTER(values, Table1[Column1]=task), col_nums>=num))
        )
    ),
    HSTACK(
        VSTACK("Remaining time planned", task_list),
        VSTACK(DROP(Table1[#Headers],, 1), results)
    )
)

Upvotes: 0

P.b

Reputation: 11628

This could also be achieved with "old" Excel functions:

Paste this in the January-column and drag along: =SUMPRODUCT(INDEX(Table1,,COLUMN(B:B)):INDEX(Table1,,13)*(Table1[[Column1]:[Column1]]=Table2[@[Remaining time planned]:[Remaining time planned]]))

(Or using absolute reference: =SUMPRODUCT(B$2:$M$15*($A$2:$A$15=$A18)) )

Upvotes: 3