excelsumifsindex-match

SUMIF rows based on selection on other table

I have been trying to get a combination of SUMIF/INDEX/MATCH to work but failed due to the probably obscure table format. Unfortunately, I cannot change the format as of now.

Table 1 contains the minutes available per shift calculated from a previous function by month:

Month Wk_day_1 Wk_day_2 Wk_day_3 Sat_1 Sat_2 Sat_3 Sun_1 Sun_2 Sun_3 Sum
Jan 23 10080 10080 10080 1920 1920 1920 2880 2880 2880 24960
Feb 23 9600 9600 9600 1920 1920 1920 1920 1920 1920 23040
Mar 23 11040 11040 11040 1920 1920 1920 1920 1920 1920 25920
Apr 23 9120 9120 9120 2400 2400 2400 2880 2880 2880 23520
May 23 10080 10080 10080 1920 1920 1920 2880 2880 2880 24960
Jun 23 10080 10080 10080 1920 1920 1920 2400 2400 2400 24480
Jul 23 5280 5280 5280 2400 2400 2400 7200 7200 7200 20160
Aug 23 10560 10560 10560 1920 1920 1920 2400 2400 2400 25440
Sep 23 10080 10080 10080 2400 2400 2400 1920 1920 1920 24480
Oct 23 10560 10560 10560 1920 1920 1920 2400 2400 2400 25440
Nov 23 10080 10080 10080 1920 1920 1920 2400 2400 2400 24480
Dec 23 9120 9120 9120 2400 2400 2400 3360 3360 3360 24000

Table 2 contains a selection possibility to define which shifts to summarise.

Shift Work
Wk_day_1 x
Wk_day_2 x
Wk_day_3
Sat_1
Sat_2 x
Sat_3
Sun_1 x
Sun_2
Sun_3

To show the intended use, I manually summarised the Wk_day_1, Wk_day_2, Sat_2, and Sun_1 in the Sum column in Table 1.

The intention is to select the shifts, marked by x in the Work column in Table 2 and summarise row-wise accordingly in the Sum column in Table 1. I appreciate the help !

Solution

  • You may try SUMPRODUCT() try below formula.

    =SUMPRODUCT(--($B$1:$J$1=TRANSPOSE(IF($B$17:$B$25="x",$A$17:$A$25,0))),B2:J2)

    enter image description here