Summarizing Text From Multiple Excel Tabs

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!