Extract data from multiple columns, group and sum up unique values

[Alcotraz]

Hello guys,

I need to find the formula which could extract unique data from multiple columns by Code, and sum up Hours, and sort it out the type,

So the result would show me the sum for unique codes , grouped into Types:

GSQ01
- Type 1 - 4
- Type 2 - 0
- Type 3 - 0
GSQ02
- Type 1 - 0
- Type 2 - 7
- Type 3 -4
GSQ03
- Type 1 - 5
- Type 2 - 2
- Type 3 - 8

( dont know if I calculated right , this is the main problem that it easy to make mistakes doing it manually.)

There would be only 3 types, and many different codes, unique and duplicates.

The document has blank rows and coulmns too, but for the start, I would be grateful if you could help me out with finding solution to this problem first.


Type Code HRS Code HRS
Type1 GSQ01 2 GSQ01 2
Type2 GSQ02 5 GSQ02 2
Type3 GSQ02 4 GSQ03 4
Type1 GSQ03 5 GSQ04 5
Type2 GSQ03 2 GSQ03 7


Many thanks,

Aivaras

[Bernie Deitrick]

Assuming that your data is in columns A to E of your sheet, as you show in your bottom table:

Select column A and copy it, then right click column D and choose Insert copied cells. Then grab from D2 to the end of column F, and drag it under the values in column A, and delete columns D to F.

Then select A:C and choose Insert / Pivot table, and place the pivot table on a new sheet. Select Code as a row field, Type as a row field, and HRS as the data field, and you are done.

[Alcotraz]

Thank you for your solution, but the problem is, that I will have new timesheet ( new timetable) everyday, and it would still take a lot of time to rearrange it. The formula I came up with is =SUMIFS($M$4:$M$10,$L$4:$L$10,E19,$K$4:$K$10,F18)
based on 2 criteria, and it works well , except that its only works for 1 column of data, not multiple, and I also need dynamic range for unique values, so every time I get different codes it would automatically extract and sort the information.

[Bernie Deitrick]

Try changing to this is get the second set of data - not sure what ZZZ, XXX, and YYY should be....

=SUMIFS($M:$M,$L:$L,$E19,$K:$K,F$18)+SUMIFS($ZZZ:$ZZZ,$XXX:$XXX,$E19,$YYY:$YYY$,F$18)