# Section wise summarize the data into one sheet from other work sheets

1. ## Section wise summarize the data into one sheet from other work sheets

Hi,

I required a formula help (Sum product or sumifs with Indirect function) to prepare a summary section wise separately by referring the "Ref Number",

values to be return automatically once entered Ref Number in cells Column A of summary sheet with including related descriptions against ref numbers.

Sample workbook enclosed for reference.

2. ## Re: Section wise summarize the data into one sheet from other work sheets

As you are using the 365 version of Excel I suggest utilizing Get & Transform (Power Query) to produce a row over row table as modeled on the PQ Table sheet.
First convert all of the ranges (A2:F11) on the daily sheets to tables (I recommend giving them meaningful names i.e. Table01.09.20).
The Power Query advanced editor code used to produce the table is:
``Please Login or Register  to view this content.``
Note that the above code will find all of the tables in the workbook.
The formula for Ref Number is: =IFERROR(INDEX(Query1[Number],MATCH(0,INDEX(COUNTIF(A\$3:A3,Query1[Number]),,),)),"")
The formula for Work Done and the first Total Qty is: =IF(\$A4="","",INDEX(Query1[Under Section],MATCH(\$A4,Query1[[Number]:[Number]],0)))
The formula for Man Power and Machinery is: =SUMIFS(Query1[Man Power],Query1[[Number]:[Number]],\$A4,Query1[[Section]:[Section]],\$D\$1)
I don't understand what you want for the second the third Qty columns.
Let us know if you have any questions.

3. ## Re: Section wise summarize the data into one sheet from other work sheets

Hi,

Thanks a lot for your power query solution.

However this will be a use full solution, question here is this will work on large data like full month date wise sheets as i showed in sample sheet.

As you asked above about second and third QTY columns, in a work sheet there are 3 sections as like (Section A and B,C),as based on section by referring the Ref number sum values to be return equally each section wise. as like in each section returns sums values of Manpower and Machinery the same way QTY sums values also returns each section wise.

Thanks,

4. ## Re: Section wise summarize the data into one sheet from other work sheets

It should work with any number of tables in a workbook.
The formula for the Qty columns could be similar to: =IF(\$A4="","",SUM(D4:E4))
Let us know if you have any questions.

5. ## Re: Section wise summarize the data into one sheet from other work sheets

Hi,

good to know about it should work many number of sheets in a workbook, its a automate process, once only we can arrange in proper sequence.

actually i am looking a formula for Column Total quantity in summary here is:=IF(\$A5="","",SUMIFS(Query1[Qty],Query1[[Number]:[Number]],\$A5,Query1[[Section]:[Section]],\$D\$1))

in Summary Sheet Section and Ref number wise sum the Qty Column from PQ TABLE.

any how solution done.

6. ## Re: Section wise summarize the data into one sheet from other work sheets

any how solution done...Thanks for your support.
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)