Share via

I want to create a summary table of three sheets, but only using data that meets specific criteria.

Pey 20 Reputation points
2025-12-03T20:18:44.1733333+00:00

I have three excel sheets with columns A-J. I want to make a summary table that only includes columns A, F and G. Additionally, I want to only add items to my summary that have an item in Column F.

Is there a way to do this using XLOOKUP or VSTACK? The pivot table contains too much data.

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer accepted by question author
  1. Hendrix-C 8,165 Reputation points Microsoft External Staff Moderator
    2025-12-03T21:02:49.6033333+00:00

    Hi @Pey,

    Thank you for posting your question in the Microsoft Q&A forum.

    According to your situation, you can use VSTACK combined with CHOOSECOLS to select the specific columns that you need. You can try using this formula:

    =VSTACK(FILTER(CHOOSECOLS(A1:J10,1,6,7),F1:F10<>""))

    User's image

    This formula will export only if the according cell in column F of that row contains data. It will also automatically update for you when you modify (add/delete) anything in column A,F,G.

    User's image

    Note: these formulas are using the data range in my testing worksheet. Therefore, you will need to change the data range with your current worksheet to ensure the formulas work properly.

    I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.    

    Thank you for your patience and your understanding. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. 

    I look forward to hearing from you.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have any extra questions about this answer, please click "Comment".  

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2025-12-03T20:18:56.7866667+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    To create a summary table from three Excel sheets that includes only columns A, F, and G, and filters out rows where column F is empty, you can use the FILTER function in combination with VSTACK to consolidate the data from the sheets. Here’s a general approach:

    1. Use FILTER to Select Data: For each sheet, use the FILTER function to select only the rows where column F is not empty. The formula would look something like this for each sheet: 
         =FILTER(Sheet1!A:G, Sheet1!F:F <> "")
      Repeat this for Sheet2 and Sheet3.
    2. Combine Data with VSTACK: After filtering the data from each sheet, you can combine the results using VSTACK. The formula would look like this: 
         =VSTACK(FILTER(Sheet1!A:G, Sheet1!F:F <> ""), FILTER(Sheet2!A:G, Sheet2!F:F <> ""), FILTER(Sheet3!A:G, Sheet3!F:F <> ""))
    3. Select Specific Columns: Since you only need columns A, F, and G, you can wrap the VSTACK function with another FILTER or INDEX to select only those columns: 
         =INDEX(VSTACK(FILTER(Sheet1!A:G, Sheet1!F:F <> ""), FILTER(Sheet2!A:G, Sheet2!F:F <> ""), FILTER(Sheet3!A:G, Sheet3!F:F <> "")), {1, 6, 7})
      Here, {1, 6, 7} refers to the first, sixth, and seventh columns from the stacked data, which correspond to A, F, and G respectively.

    This approach allows you to create a summary table that meets your criteria without using a pivot table, focusing only on the relevant data from the specified columns.

    References:

  2. Pey 20 Reputation points
    2025-12-03T20:43:21.6133333+00:00

    This would work I think, except some tabs don't have any data in the F column yet. Is there another way to do this?

    0 comments
  3. Ashish Mathur 100.8K Reputation points Volunteer Moderator
    2025-12-03T23:19:16.71+00:00

    Hi,

    Try this

    =filter(let(c,choosecols(vstack('sheet1:sheet3'!$A$2:$G$100),1,6,7)),choosecols(c,2)="your criteria")

    If it does not work then share the download link of the MS Excel file.

    Hope this helps.

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.