Ask Excel Help: How to add or count across multiple sheets in Excel

I recently received a question from a reader on our Excel Help Facebook page asking the following:

=SUM(First:Last!A1) adds up numbers across a series of sheets in the same workbook only if A1 contains a number. How do I add up the total number of times an A1 cell contains a text value x?

First of all, if you’re not familiar you are able to add values across different sheets using SUM function. Say you have 3 worksheet named Sheet1, Sheet 2, and Sheet3. You can use this formula to add up all the values in cell A1 in all three sheets:

=SUM(Sheet1:Sheet3!A1)

This special syntax is referred to as a 3D Reference.

Remember, SUM adds all the numbers in a range. You can type in the formula manually, or, after typing “=SUM(“ hold down the shift key and select the sheets, then select the cells to sum. To sum a range of numbers across sheets rather than an individual cell, the formula would look like this:

=SUM(Sheet1:Sheet3!A1:B16)

If your sheet names are numbers, add apostrophes to the formula:

=SUM(‘1:3’!A1:B16)

So our reader was asking how to count cells with text rather than adding numerical values. COUNT function will count the cells containing numbers, while COUNTA will count the number that are not empty. Same formula as before but replace SUM with COUNTA:

=COUNTA(Sheet1:Sheet3!A1)

This method works for almost any other function, too, such as AVERAGE. However, if you try using SUMIF or COUNTIF you’ll probably get a #VALUE error. Using those functions requires a bit more work using named ranges and INDIRECT, a topic for another day.

If you ever have any Excel questions or need any Excel help please feel free to send them my way! I read each and every message or comment I receive.