Excel Adjacent Formula

0
votes

I am desperately trying to find a formula that solves my problem.

Sheet1 structure:

name   date         weight

John   31.07.2020   80
Jack   01.08.2020   75
...

Sheet2 structure:

date         John   Jack ....
31.07.2020
01.08.2020

What I need to do is, for every date and name combination in Sheet2, I need to copy the weight information from Sheet1 into name's column of Sheet 2.

Example outcome:

date         John   Jack ....
31.07.2020    80
01.08.2020          75

I have used a couple of formulas but I could not make it work.

Can you help me with this?

Thank you

2
excelexcel-formula
You can use SUMIFS. Or a pivot table. – BigBen
Thank you @BigBen can you explain it little bit more? – Falzao

2 Answers

1
votes

First, turn your data entry table into an Excel Table object by using Ctrl-T or Insert > Table.

Then, with one cell of that table selected, use Insert > Pivot Table.

Drag the Date into the Rows area, drag the Name into the Columns area and the Weight into the Values area. Format the pivot table to have no grand totals (Design Ribbon visible when a cell in the pivot table is selected).

enter image description here

When you add new data to the data entry table, the Table object will expand automatically and you can simply refresh the pivot table to include the new data.

1
votes

It's really quite simple with SUMIFS.

If Sheet 1 looks like this:

Sheet1

...then you use this formula:

=SUMIFS(Sheet1!$C$2:$C$3,Sheet1!$A$2:$A$3,Sheet2!B$1,Sheet1!$B$2:$B$3,Sheet2!$A2)
=SUMIFS(Sheet1!R2C3:R3C3,Sheet1!R2C1:R3C1,Sheet2!R1C,Sheet1!R2C2:R3C2,Sheet2!RC1)
=SUMIFS(Table1[weight],Table1[name],Sheet2!B$1,Table1[date],[@date])
=SUMIFS(Table1[weight],Table1[name],Sheet2!R1C,Table1[date],[@date])

I get this:

Sheet2