CODEWITHSUNDEEP
excel
Jesper.Lindberg
Jesper.Lindberg

Reputation: 339

Find multiple matching rows and then return the matching sub-row

So I am having the following setup.

sheet 2023:

2023-sheet

sheet 2023-raw:

2023-raw-sheet

In the 2023-sheet I would now like to populate the cells with my working hours for that specific date. My way of thinking and trying to create an excel formula for this follows:

  1. If matching week (vecka) exists in 2023-raw.
  2. Then check if the current column name in 2023 sheet exists in the previous matched rows.
  3. Make the calculations base of the return value in step 2.

So basically I have issues with getting the value from step 2. MATCH also only returns the first matching row? I would like to get a subset of all matching rows and then check again if the column name can be found in those rows.

Upvotes: 0

Views: 344

Answers (3)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

Just use SUMIFS to make calculus. SUMIFS will allow you to return a number based on criteria. If you are trying to calculate working ours (OUT minus IN) you can do it.

I made a fake dataset similar to yours:

enter image description here

Formula in cell B2 is:

=SUMIFS($O$2:$O$6;$L$2:$L$6;$A2;$M$2:$M$6;B$1)-SUMIFS($N$2:$N$6;$L$2:$L$6;$A2;$M$2:$M$6;B$1)

In case you need the output in decimal form, just multiply by 24, like the second example. Formula in cell B13 is:

=24*(SUMIFS($O$2:$O$6;$L$2:$L$6;$A2;$M$2:$M$6;B$1)-SUMIFS($N$2:$N$6;$L$2:$L$6;$A2;$M$2:$M$6;B$1))

In both cases just drag down and to right and the formula will work.

Please, notice this is based on the data you provided. If your working schedules sometimes go ahead 00:00 maybe you'll need to adjust a little bit.

Upvotes: 1

user11222393
user11222393

Reputation: 5471

OP found a solution using a proposed FILTER formula in comment and asked to post it as an answer :

=INDEX(FILTER('2023-raw'!B2:G53, ('2023-raw'!B2:B53=A47) * ('2023-raw'!C2:C53=C1), "h"), , 4) - INDEX(FILTER('2023-raw'!B2:G53, ('2023-raw'!B2:B53=A47) * ('2023-raw'!C2:C53=C1), "h"), , 3) -  INDEX(FILTER('2023-raw'!B2:G53, ('2023-raw'!B2:B53=A47) * ('2023-raw'!C2:C53=C1), "h"), , 5) -  INDEX(FILTER('2023-raw'!B2:G53, ('2023-raw'!B2:B53=A47) * ('2023-raw'!C2:C53=C1), "h"), , 6)

Upvotes: 1

Ike
Ike

Reputation: 13014

If you have Excel 365 you can use this `MATRIX

=MAKEARRAY(4,3,LAMBDA(r,c,
             SUMPRODUCT(tblData[Hours worked]*
                        (tblData[Week]=r)*
                        (tblData[Day]=INDEX(B8:D8,1,c)))

It will spill down - so that you can't use it in a table:

enter image description here

I simplified my example using a "hours worked" column - so you might add a new column to your table or you have to calculate the hours in the formula.

Upvotes: 1

Related Questions