Sum based on pattern match for multiple rows

Question

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

Do you define these PID for car types? Do you have any other table for these keyword? — Harun24hr, Jul 04 '22 at 08:30
How excel will know which `PID` for which type of cars? What is your actual excel version? You have tagged 2010 & 2019. — Harun24hr, Jul 04 '22 at 08:32
@Harun24hr - Yes, I define these PIDs. It is in another table. — The Great, Jul 04 '22 at 08:34
You should be able to sum up with SUMIFS but using as criteria wildcars. Something like `=SUMIFS($C$2:$C$5;$B$2:$B$5;"PID21*")` will sum up values in a column based on another column **starting** with the text PID21. If your PIDs are constant and not change, you may use it — Foxfire And Burns And Burns, Jul 04 '22 at 08:34
@FoxfireAndBurnsAndBurns - How do I key in multiple PIDs in wildcars? Should I repeat the same formula that you wrote multiple times (depending on no of PIDs)? — The Great, Jul 04 '22 at 08:35
In order to be helped with a solution, "Yes, I define these PIDs. It is in another table." statement is at least strange... Please edit your question and show the sheet you are talking about. I will give more eloquence to your question. Is it there equivalences between these 'PIDs` and categories in column B:B? — FaneDuru, Jul 04 '22 at 08:39
So write versions for PID43, PID76, PID152 or just use the numbers 43, 76 if they are unique. Have a go at testing as you have been given a flexible solution. — Solar Mike, Jul 04 '22 at 08:41

Foxfire And Burns And Burns · Accepted Answer · 2022-07-04T08:50:14.017

2

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!

edited Jul 04 '22 at 08:50

answered Jul 04 '22 at 08:44

Foxfire And Burns And Burns

10,665
2
17
38

nice. will try now. Just a question out of curiosity to learn excel. So, can this problem be solved any better/faster using macro? Or formula is the best for this problem? – The Great Jul 04 '22 at 08:46
@TheGreat please, check updated answer – Foxfire And Burns And Burns Jul 04 '22 at 08:50
Only problem is in my real data, we cannot have `A` column like what you have got due to strictly formatted sheets etc (i can't modify that). Nonetheless, I will use your 1st solution. – The Great Jul 04 '22 at 09:04

Sum based on pattern match for multiple rows

1 Answers1