Share via

SUMIF, VLOOKUP, MATCH FORMULA TO CREATE A SUMMARY FROM DATA ENTERED IN A SPREADSHEET

Anonymous
2021-04-19T19:32:51+00:00

Hi

I am trying to get the summary at the top of the page linked to the data entry at the bottom of the page.

Data entry happens in Column D, M, N, O

Column D is parts used (-)

Column M is parts ordered (+)

Column N is parts received (-)

Column O is parts ordered (+)

The summary section is fed by the following:

Col D - 'Stock Packs and Drawing/Type' 

Col M - 'Stock Packs and Bar/Tube' 

Col N - 'Stock Packs and Bars/Tubes received'

Col O - 'Stock Packs and Bars/Tubes and Quantity Produced'

What formula should I use to sum, vlookup and match data in the summary section?

the file is located here file

I would be grateful for any help!

Thanks

Michelle

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2021-04-21T22:48:16+00:00

Hi Miche,

Thank you for your reply.

If you would like to hide the zero value and show as an empty cell. You can change to settings in Excel app by File > Options > Advanced > Under Display options for this worksheet > uncheck “Show a zero in cells that have zero value”.

For your information:

Display or hide zero values 

You can also further specific this setting for a particular worksheet/workbook. If you only want to hide zero value for Summary sheet.

 

Best regards,

Dihao

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2021-04-21T01:14:15+00:00

Hi Miche,

Thank you for your reply and further clarification.

Perhaps this the outcome you are looking after in your summary table?

I noticed that in your Data enter sheet, some data entries are not listed, such as Stock Pack 18 -94998, Stock Pack 13-95071, which there are some difference from your example of output. Hence the formula I used only calculate the data you provided in “Shopfloor-using” table and “Office-Receiving/Ordering”

 

Based on my understanding, you are trying to track the stocks left in Summary table, which is sum up all the data in “OFFICE - RECEIVING/ORDERING” table and minus the sum of  “Qty used” in “SHOPFLOOR – USING” table”

In this scenario, I am using SUMPRODUCT formula first to sum all records for “Bars/Tubes Qty on  Order”, “Bars/Tubes Received” and “Quantity Produced” in “OFFICE - RECEIVING/ORDERING” table based on the category of “Stock Pack” and “Bar/Tube”, which it will get all the stocks you ordered, received and Produced.

Then using SUMIFS formula to sum all the data for “Qty used” in “SHOPFLOOR – USING” table to figure out the total used product based on the category of “Stock Pack” and “Bar/Tube”

After that just minus the results from two formulas, which it get the stock summary in the summary table.

Here is a file I edited based on your shared file. Hope it can give you a clear picture with the formula I used.

Note: I added some new data in Data entry sheet for testing the formula.

MICHE STOCK edited.xlsx

Best regards,

Dihao

Was this answer helpful?

1 person found this answer helpful.
0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-04-21T19:16:34+00:00

    Hi 

    This is brilliant thank you, is there a way of showing an empty cell if the balance is zero?

    Thanks

    Miche

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-04-20T07:08:46+00:00

    My apologies Dihao, I have now removed the additional sheets.  The summary section is at the top of the DATA ENTRY sheet in rows 2 to 24.  Hope that helps simplify?  I have added an output sheet that shows what should be showing in the summary columns - maybe that will help too?

    Columns E2-L24 are just duplicating the results in Column C whereas they should have their own results based on the Stock Pack

    Columns M, N, O are for adding stock in one form or another

    Column C is for using stock

    Please let me know if I can give you anymore info.

    Thanks a million

    Miche

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-04-20T06:22:05+00:00

    Hi Michelle,

    Thank you for posting your question in this community.

    Based on your description, I understand that you would like to find the match data from “Data Entry” sheet and record into “Summary” sheet. But from your sample data you shared above, I didn’t find any exact match product that you listed under “POWDER TYPE” in “Summary sheet” from “Data Entry either in “SUMMARY(2)” sheet.  Perhaps, we miss out something?

    If it is convenient, could you share more details or a sample file with expected outputs, so that we can understand how your workbook is designed and data is calculated. Thank you.

    Additionally, it seems that your workbook link contains other reference data from other workbook, as the worksheets “Stock Pack#” “Summary” have “#REF!” error when I opened it from my side. Not sure whether these referring data would involve the calculation.

    Best regards,

    Dihao

    Was this answer helpful?

    0 comments