Sum based on pattern match for multiple rows-CodePudding

I have an excel input data like below

purchase revenue          FY_1920   FY_2021 FY_2122
PID21 kids & adults (KA)    75        75     80
PID21Elderly and old (EO)   75        75     80
PID76Men or boys            80        75     80
PID52 Women or ladies       100       25     100
Total                       330       250    340

which looks like below

Now, I would like to fill the below output table based on below logic

Fill in cars row based on keywords PID21 and PID24 of input table

Fill in Electric Vehicle row based on values of keywords from PID43, PID76, PID152 of input table

criteria table looks like below

I was trying something like below with the help of online support

SUMIFS(C$21:C$25, $B$21:$B$25, INDEX(Sheet1!$A:$A, LARGE(IF(Sheet1!$B:$B = Input!$B5, ROW(Sheet1!$B:$B), ""),1))

I expect my output to be like as below

CodePudding user response：

IF your PIDs criteria a few and do not change, you may use several SUMIFS combined:

for cars output formula is:

=SUMIFS(C$2:C5;$B$2:$B$5;"PID21*") SUMIFS(C$2:C$5;$B$2:$B$5;"PID24*")

for electric formula is:

=SUMIFS(C$2:C$5;$B$2:$B$5;"PID43*") SUMIFS(C$2:C$5;$B$2:$B$5;"PID76*") SUMIFS(C$2:C$5;$B$2:$B$5;"PID52*")

Notice both formulas got first argument with mixed references, so you just need drag to right the formula.

UPDATE: Now that i saw you got your PID's somehwere else, I would suggest you to use this structure:

Notice the PID'S at most right are not using combined cells (that's a terrible idea because it affects formulation). So first you get in your data what kind of BL is with:

=VLOOKUP(LEFT(B2;5);$I$2:$J$6;2;FALSE)

Then you do again SUMIFS but based on that column, so much easier:

=SUMIFS(C$2:C$5;$A$2:$A$5;$B10)

Just drag to right and down and you'll get all output!