# Excel - SUM Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a work book with multiple sheets. I need to search all sheets for specific text, and sum the quantities. This is an estimate form with multiple sheet that I am trying to generate a Bill of Material from the data on these multiple sheets.
Solved by K. J. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 07/08/2018 - 01:49
Hi
User 07/08/2018 - 01:50
hello
Excelchat Expert 07/08/2018 - 01:50
can you share the excel please
User 07/08/2018 - 01:53
this is a basic example of what I am trying to acomplish
User 07/08/2018 - 01:54
I need a formula to look for the "description" in all sheets
Excelchat Expert 07/08/2018 - 01:54
give me few minutes.. let me check the data..
User 07/08/2018 - 01:54
and generate a it in the BOM sheet with sums
Excelchat Expert 07/08/2018 - 01:55
Ok... help me if my understanding of the problem is correct..
Excelchat Expert 07/08/2018 - 01:56
You need a BoM sheet which would pick description from each sheet and then provide the sum of cost?
User 07/08/2018 - 01:56
correct, but provide sum of QTY needed
Excelchat Expert 07/08/2018 - 01:57
ok..
User 07/08/2018 - 01:57
basically so we can auto generate a BOM base off of multiple sheets allowing us to send the BOM sheet to distribution for pricing.
Excelchat Expert 07/08/2018 - 01:58
will the description be on column A across the sheets?
User 07/08/2018 - 01:58
not all sheets
User 07/08/2018 - 01:58
the descriptions will change per sheet
User 07/08/2018 - 01:58
some will mate sheet one, some will be totally different
User 07/08/2018 - 01:59
match*
Excelchat Expert 07/08/2018 - 01:59
How many sheets do you have?
User 07/08/2018 - 01:59
an estimate could have up to 20
User 07/08/2018 - 01:59
or more
User 07/08/2018 - 02:00
some may only have 3-5
Excelchat Expert 07/08/2018 - 02:01
ok.. will the keyword "description" be on row-2?
User 07/08/2018 - 02:01
yes
User 07/08/2018 - 02:01
on every sheet
User 07/08/2018 - 02:01
Headers will be identical on every sheet
Excelchat Expert 07/08/2018 - 02:02
ok..so after the column Description there will be QTY column.. and we will build a sum of the QTY
User 07/08/2018 - 02:02
correct
Excelchat Expert 07/08/2018 - 02:03
ok.. give me sometime to build a solution..
User 07/08/2018 - 02:20
any luck? I have been banging my head against the desk trying to figure this one out
Excelchat Expert 07/08/2018 - 02:20
Hey.. i'm trying to see if there is a formula based approach..
User 07/08/2018 - 02:21
no problem....I know this is more complicated
User 07/08/2018 - 02:21
and appreciate the help
Excelchat Expert 07/08/2018 - 02:21
i know there is VBA approach.. but we are not supporting VBA in this platform..
User 07/08/2018 - 02:21
ahh
Excelchat Expert 07/08/2018 - 02:21
we have another 20mins for this session..
User 07/08/2018 - 02:22
yea more than likely not enough time :)
Excelchat Expert 07/08/2018 - 02:22
:)
Excelchat Expert 07/08/2018 - 02:26
How familier are you with excel??
Excelchat Expert 07/08/2018 - 02:26
i think we might have a non-vba solution...
User 07/08/2018 - 02:27
somewhat
Excelchat Expert 07/08/2018 - 02:27
ok.. lets give a try..
Excelchat Expert 07/08/2018 - 02:27
i will ping you the steps.. in few mins
User 07/08/2018 - 02:27
ok
Excelchat Expert 07/08/2018 - 02:35
The non VBA solution I was thinking is..
Excelchat Expert 07/08/2018 - 02:36
Step-1.. across the sheets remove all the blank rows.
Excelchat Expert 07/08/2018 - 02:36
Steps-2 : Convert the data in each of the sheets o a table
Excelchat Expert 07/08/2018 - 02:37
Step-3 : insert a pivot table..
Excelchat Expert 07/08/2018 - 02:38
IN the pivot table fields you have an option More Table..
Excelchat Expert 07/08/2018 - 02:39
this would give you all the tables created in step-2
Excelchat Expert 07/08/2018 - 02:40
Step-4 : Build a relationship between description across each of the tables..keeping one of the table as primary..
Excelchat Expert 07/08/2018 - 02:40
Step-5 : Drag the description field to the row..
Excelchat Expert 07/08/2018 - 02:41
Step 6 : get the QTY from each of the tables into the Values area..
User 07/08/2018 - 02:43
but as data is changed on the sheet will it update in the pivot table?
Excelchat Expert 07/08/2018 - 02:43
CHeck sheet-5 in the attached excel
Excelchat Expert 07/08/2018 - 02:43
yes.. you need to refresh the pivot
Excelchat Expert 07/08/2018 - 02:43
to do that.. right click on the pivot.. and click "Refresh"...
Excelchat Expert 07/08/2018 - 02:44
it will fetch the latest data across.. all the tables..
User 07/08/2018 - 02:46
perfect, I certainly appreciate it
Excelchat Expert 07/08/2018 - 02:46
Are you able to follow..
User 07/08/2018 - 02:47
yes
Excelchat Expert 07/08/2018 - 02:47
I have marked the session as done..
User 07/08/2018 - 02:47
Excelchat Expert 07/08/2018 - 02:47
please leave a feedback when you close the session..
Excelchat Expert 07/08/2018 - 02:48
It was a challenging problem to work on..
Excelchat Expert 07/08/2018 - 02:48
have a nice evening.. good luck..
User 07/08/2018 - 02:48
you too!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.