Forum Discussion

Santrey's avatar
Santrey
Copper Contributor
Nov 10, 2025

Summarizing Text From Multiple Excel Tabs

I have created a workbook with several tabs.  I have a tab where I want to capture a summary of data from the other tabs.  The data I want to summarize is in cells where I have used data validation to create drop down boxes (Y/N). I have tried to summarize the number of Y's and N's using SUM and COUNTIF but neither have worked.  I've looked at YouTube videos to help to no avail.  I have even tried to do add the text in each cell manually and that didn't work either. I am not an expert in Excel so can someone please tell me what I'm doing wrong.  I've been trying to figure this out for a couple of days.  

excel
Formulas and Functions

2 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Nov 11, 2025

    See this discussion

    https://techcommunity.microsoft.com/discussions/excelgeneral/combine-table-name-with-each-value-in-the-first-column/4434093/replies/4434125

  • kspittler12's avatar
    kspittler12
    Copper Contributor
    Nov 10, 2025

    To summarize Y/N values across multiple tabs, use a formula like =SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),"Y")), where SheetList is a named range of your sheet names. This works even with dropdowns

    To summarize Y/N values across multiple tabs, use a formula like =SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),"Y")), where SheetList is a named range of your sheet names. This works even with dropdowns.

    Let’s walk through this step-by-step so you can get it working without needing to be an Excel expert:

    🧩 Why SUM and COUNTIF Might Not Have Worked

    • SUM only works with numbers, not text like "Y" or "N".
    • COUNTIF works with text, but it doesn’t automatically span multiple sheets unless you use a more advanced setup.
    • Dropdowns (data validation) don’t affect formulas directly—they’re just a way to restrict input. So formulas like COUNTIF still work as long as the cell contains "Y" or "N".

    ✅ How to Count Y/N Across Multiple Tabs

    Here’s the best method using COUNTIF and INDIRECT:

    1. Create a list of your sheet names

    On your summary tab, list the names of the sheets you want to pull data from. For example, in cells A2 to A5:

    Sheet1 Sheet2 Sheet3 Sheet4

    2. Name that range

    Select the cells with sheet names → go to the Name Box (top-left of Excel) → type SheetList → press Enter.

    3. Use this formula to count Y’s

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),"Y"))

    • Replace A1:A10 with the actual range where your dropdowns are.
    • This formula loops through each sheet in SheetList and counts the "Y"s.

    4. To count N’s, just change "Y" to "N":

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),"N"))

    🛠️ Common Pitfalls to Avoid

    • Sheet names must be exact (no typos).
    • Ranges must exist in all sheets (e.g., if Sheet2 doesn’t have A1:A10, it will error).
    • Don’t use merged cells in your dropdown range—they confuse formulas.
    • Make sure cells contain only "Y" or "N"—extra spaces or lowercase letters will break the match.

    🧠 Bonus Tip: Make It Dynamic

    If your dropdowns are in different ranges on each sheet, you’ll need to adjust the formula for each one or use VBA (which is more advanced). But if they’re consistent, this method is solid.

    Would you like help setting up a sample workbook or troubleshooting your formula? I can walk you through it!

Resources