Help with grouping and consolidation

Hi,

I wasn't sure which sub-forum this should be asked in, hence posting it in the general forum.

I have data from invoices that are spread over the years. It has Year, Item number, Net sales and Quantity as rows.
You can see it in sheet1 of the attached excel.

What I need is consolidation and grouping by year like in sheet2 of the attachment.

I'm not sure if this requires writing macros, simple forumals, etc... but any help pointing towards the direction will be much appreciated

Thanks,
Rahul

consolidation.xlsx

This in Sheet 2 B2 copied across should get you started:

=SUMIFS(Sheet1!$B3:$S3,Sheet1!$B$1:$S$1,Sheet2!B$1,Sheet1!$B$2:$S$2,"Type1")

Copy down and change Type 1 as appropriate.

Late edit: in D2 you will need =SUMIFS(Sheet1!$B3:$S3,Sheet1!$B$1:$S$1,Sheet2!B$1,Sheet1!$B$2:$S$2,"Type2") and in F2 =SUMIFS(Sheet1!$B3:$S3,Sheet1!$B$1:$S$1,Sheet2!B$1,Sheet1!$B$2:$S$2,"Type3") and so on.

@AliGW I don't think it would work.

@rahul as per your requirement

try B2=SUMPRODUCT((IF(LEFT($A2,1)="N",REPLACE(LEFT($A2,FIND(" ",$A2)-1),4,0," "),LEFT($A2,FIND(" ",$A2)-1))=Sheet1!$A$3:$A$4)*(SUBSTITUTE(TRIM(RIGHT($A2,LEN($A2)-FIND(" ",$A2)))," ","")=Sheet1!$B$2:$S$2)*(Sheet2!B$1=Sheet1!$B$1:$S$1)*(Sheet1!$B$3:$S$4))

Drag over and down.

Formula:

=SUMPRODUCT((IF(LEFT($A2,1)="N",REPLACE(LEFT($A2,FIND(" ",$A2)-1),4,0," "),LEFT($A2,FIND(" ",$A2)-1))=Sheet1!$A$3:$A$4)*(SUBSTITUTE(TRIM(RIGHT($A2,LEN($A2)-FIND(" ",$A2)))," ","")=Sheet1!$B$2:$S$2)*(Sheet2!B$1=Sheet1!$B$1:$S$1)*(Sheet1!$B$3:$S$4))

Originally Posted by shukla.ankur281190

@AliGW I don't think it would work.

I can assure you that it does work in the file provided by the OP, and he has also said that it works "like a charm".

You're very welcome! Your locale requires the ";", mine the ",". Glad it worked for you.

Originally Posted by shukla.ankur281190

Yes It would work only for fist two rows but the next will give you 0 as sumrange you have taken Sheet1!$B3:$S3 and you have locked the columns not the row, however If Rahul is with this solution then its O.K.

Thanks
Ankur

Your point is correct, yes, and I omitted to mention it in my post, but I believe that Rahul worked it out. I will edit my post.