0
A B C
1 User Task Hours
2 Jim AA-1 10
3 Mike AA-2 12
4 Jim AA-3 13
5 Steve CC-5 14
6 Jim BB-1 15
7 Mike BB-3 5
8 Steve BB-4 10
9 Mike CC-5 8

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.

Improve this question

2 Answers 2

Reset to default
1

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,"-"))))&"*"

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hey, thanks a lot for your answer but maybe I wasn't clear enough. G2 should have AA*, BB* and CC*. The main issue is that we have about 60 different type of tasks (AA*...) and we have to create different groups (product 1 contains tasks from AA, BB, CC, DD ...) so grouping products is hard. However just writing this I thought of a solution. Thanks a lot.
0

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

As I stated above I have the following table:

A B C
1 User Task Hours
2 Jim AA-1 10
3 Mike AA-2 12
4 Jim AA-3 13
5 Steve CC-5 14
6 Jim BB-1 15
7 Mike BB-3 5
8 Steve BB-4 10
9 Mike CC-5 8

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:

A B
1 Group Task
2 a AA*
3 a BB*
4 b CC*
5 a DD*

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.

Improve this answer

2 Comments

In this post second table is your desired result?
So in this second table, with the column group I can then create a column D in the first table that represents which group each type of task belongs to and then I can do the sum based on the group.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.