Sum each row with based on Col A of Data Sheet

[paradise2sr]

Hi,

How can I get the result in as mentioned in 'Result' Sheet ? .My raw data is in 'Data' sheet.Lookup column is A.

[AliGW]

If you have the latest (beta) version of 365:

=VSTACK(A1:G1,HSTACK(UNIQUE(A2:B10),DROP(DROP(GROUPBY(A2:A10&B2:B10,C2:G10,SUM),,1),-1)))

[paradise2sr]

If you have the latest (beta) version of 365:

=VSTACK(A1:G1,HSTACK(UNIQUE(A2:B10),DROP(DROP(GROUPBY(A2:A10&B2:B10,C2:G10,SUM),,1),-1)))

Oops no latest beta version.Sadly it didn't worked in my version of 365.

[CARACALLA]

C2=IFERROR(IF(Result!$A2<>"",SUMIFS(INDEX(Data!$C$2:$G$100,,MATCH(Result!C$1,Data!$C$1:$G$1,0)),Data!$A$2:$A$100,Result!$A2,Data!$B$2:$B$100,Result!$B2),""),"")

Copy across and down

[paradise2sr]

C2=IFERROR(IF(Result!$A2<>"",SUMIFS(INDEX(Data!$C$2:$G$100,,MATCH(Result!C$1,Data!$C$1:$G$1,0)),Data!$A$2:$A$100,Result!$A2,Data!$B$2:$B$100,Result!$B2),""),"")

Copy across and down

No,I want in the entire result in the same format perhaps spill rather than copy across & below.

[AliGW]

All you have to do is change your update channel to the beta channel: Account > change to Miscrosoft 365 Insider, then run an update.

[Glenn Kennedy]

If you don't have the beta version, or need to share it with other O365 users who do not have the beta version, then use:

=LET(

A,Data!A2:B10,

B,Data!C2:G10,

C,UNIQUE(INDEX(A,SEQUENCE(ROWS(A)),{1,2})),

VSTACK(A1:G1,HSTACK(C,DROP(REDUCE("",INDEX(C,,1),LAMBDA(x,y,VSTACK(x,BYCOL(B,LAMBDA(z,SUMIF(INDEX(A,,1),y,z)))))),1))))

[paradise2sr]

Mindblowing Glenn.You did it.

Thanks.

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[romperstomper]

Glenn,
Why UNIQUE(INDEX(A,SEQUENCE(ROWS(A)),{1,2})) rather than just UNIQUE(A) out of interest?

[Glenn Kennedy]

Glenn,
Why UNIQUE(INDEX(A,SEQUENCE(ROWS(A)),{1,2})) rather than just UNIQUE(A) out of interest?

No coffee yet.

Actually A originally referred to columns A:G. I changed its definition, but didn't change the unique bit correspondingly. Again, caused by no coffee.

[romperstomper]

No coffee yet.

Actually A originally referred to columns A:G.

I figured it would be something like that (on both counts), but wanted to check I wasn't missing something. Thanks.

[paradise2sr]

I little bit changes done in Glenn formula referencing header to the data sheet and works like a charm.

=LET(
A,Data!A2:B10,
B,Data!C2:G10,
C,UNIQUE(INDEX(A,SEQUENCE(ROWS(A)),{1,2})),
VSTACK(Data!A1:G1,HSTACK(C,DROP(REDUCE("",INDEX(C,,1),LAMBDA(x,y,VSTACK(x,BYCOL(B,LAMBDA(z,SUMIF(INDEX(A,,1),y,z)))))),1))))

[paradise2sr]

Thanx Ali & Caracalla

[Glenn Kennedy]

Now replete with coffee...

use this:

=LET(
A,Data!A2:B10,
B,Data!C2:G10,
C,UNIQUE(A),
VSTACK(A1:G1,HSTACK(C,DROP(REDUCE("",INDEX(C,,1),LAMBDA(x,y,VSTACK(x,BYCOL(B,LAMBDA(z,SUMIF(INDEX(A,,1),y,z)))))),1))))

[paradise2sr]

Hi,Glenn, I came across one more things.My A column data must be unique,Sometimes what happen B column name might be different but A column code is always the unique.So currently I am have used your formula at A1 Sheet in which it is displaying Rose twice as one of the B column data is 'Roses' so I want to skip based on unique data already achieved at A3.So there seems to be duplicate in such type of cases where one or more B Column name might be different but A column data is unique irrespective of name in B column.This happens frequently.I have given a sample example of Rose & Roses only. I hope you understand this.

Hence,kindly modify your code accordingly and I have mentioned the actual result at I2:O6 of Result worksheet.

[Glenn Kennedy]

tRY:

=LET(A,Data!A2:G10,B,Data!A1:G1,C,UNIQUE(INDEX(A,,1)),D,XLOOKUP(C,INDEX(A,,1),INDEX(A,,2)),E,DROP(REDUCE("",C,LAMBDA(x,y,VSTACK(x,BYCOL(DROP(A,,2),LAMBDA(z,SUMIF(INDEX(A,,1),y,z)))))),1),VSTACK(B,HSTACK(C,D,E)))

[paradise2sr]

Thanx Glenn.

[Czeslaw]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"test", "Name/Date"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"test", "Name/Date", "Attribute"}, {{"sum", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "sum")
in
    #"Pivoted Column"