The table is way bigger and there are more than just AA, BB and CC type of tasks.

I want to be able to get how many hours Jim spent on tasks that start by AA* or BB*

This is simple with a sumifs but the problem is when I have 20 different type of tasks and I Want to get a lot of people results.

So I want to get in a row how many hours Jim spent on AA, BB and CC tasks and in the next row how many he spent on DD, EE, FF.

Basically I would like a sumif like (just look at the last part):

('SHEET1'!C:C,'SHEET1'!E:E,$B$3,'SHEET1'!G:G,"AA*,BB*,CC*")

Or even better if the AA*,BB*,CC* part were in another cell to easily change it.

CodePudding user response：

Try the following formula-

=SUMIFS($C$2:$C$9,$A$2:$A$9,$F3,$B$2:$B$9,G$2)

You may also use following formulas.

F3==UNIQUE(A2:A9)
G2==TRANSPOSE(SORT(UNIQUE(TEXTSPLIT(B2:B9,"-"))))&"*"

CodePudding user response：

So thanks to Harun24hr answer I started to think in doing it in different steps.

As I stated above I have the following table:

The issue is that I might have a lot of different type of tasks and I need to group some results, so I created another table to stablish the groups:

This new table is easier to maintain and I can then add a new column on the main table that represents the group based on this grouping table and then do the calculation with a simple sumif.

Thanks a lot for all the help.