SUMIF Across Multiple Sheets in Google Sheets

In this tutorial, you’ll learn a neat trick to use SUMIF across multiple sheets in Google Sheets — without relying on any helper ranges.

You might think you can simply stack or combine the criteria range and sum range from different sheets and feed them directly into SUMIF.
Unfortunately, that doesn’t work — because SUMIF requires actual ranges, not virtual ones.

Instead, we’ll list the sheet names in a column, apply SUMIF (or SUMIFS) to each sheet using MAP, and then sum all the results together.
This is the simplest and most dynamic way to perform SUMIF across multiple sheets in Google Sheets.

Table of Contents

Sample Data: Daily Expense Sheets

Assume your daily expense data is spread across multiple sheets in a workbook, and “Internet” is one of the expense categories.

The daily entries are stored in three sheets: “April 23,” “May 23,” and “Jun 23.”

Expense data across April, May, and June sheets

You can copy my sample sheet using the button below to follow along:

Copy the Sample Sheet

Now, let’s learn how to use SUMIF across multiple sheets to calculate the total expense for the item “Internet.”

Steps to Use SUMIF Across Multiple Sheets in Google Sheets

Before we start, create a new sheet (let’s call it “Summary”) to list your sheet names, enter the criteria, and apply the formula.
To add a new sheet, click the “+” button at the bottom-left corner of Google Sheets.
We’ll leave your original data sheets untouched.

Step 1: Add the Sheet Names in the Summary Sheet

In column A (starting from A2), enter your sheet names.
In this example, type:

April 23
May 23
Jun 23

Step 2: Enter the Criteria

In an empty cell (for example, C2), enter your criterion — such as Internet.

Step 3: Perform SUMIF Across Multiple Sheets in Google Sheets

In D3, enter the following formula:

=SUM(MAP(A2:A4, LAMBDA(r, SUMIF(INDIRECT(r&"!B2:B"), C2, INDIRECT(r&"!C2:C")))))

SUMIF Across Multiple Sheets formula result in Google Sheets

Here:

B2:B → Criteria range in each sheet
C2:C → Sum range in each sheet

Yes — to perform SUMIF across multiple sheets, we’ve used a combination of SUMIF + MAP + SUM.

Let’s break this down.

Formula Explanation: How SUMIF Across Multiple Sheets Works

To apply SUMIF to the first sheet indirectly (using the sheet name in A2), use:

=SUMIF(INDIRECT(A2&"!B2:B"), C2, INDIRECT(A2&"!C2:C"))

To apply this logic to each sheet in your Summary list, use the MAP function:

=MAP(A2:A4, LAMBDA(r, SUMIF(INDIRECT(r&"!B2:B"), C2, INDIRECT(r&"!C2:C"))))

Example of MAP and SUMIF formula looping through sheet names

The MAP function loops through each sheet name in A2:A4, applying the SUMIF formula to each.
This returns an array of totals — one per sheet.

Finally, we wrap it inside SUM(…) to get the overall total across all sheets.

That’s how the SUMIF across multiple sheets formula works!

Additional Tips for Using SUMIF Across Multiple Sheets

In the example above, we used three sheet names — A2:A4.

If your list of sheet names keeps growing, make the formula dynamic by replacing A2:A4 with:

TOCOL(A2:A, 1)

This automatically includes the entire column A, while ignoring empty cells.

FAQs on SUMIF Across Multiple Sheets

1. Can I use SUMIFS instead of SUMIF?

Yes! You can easily extend the logic to multiple criteria using SUMIFS.
For example:

=SUM(
  MAP(
    TOCOL(A2:A,1),
    LAMBDA(r,
      SUMIFS(
        INDIRECT(r&"!C2:C"),
        INDIRECT(r&"!B2:B"), C2,
        INDIRECT(r&"!A2:A"), ">"&D2
      )
    )
  )
)

Here, it sums column C where:

Column B equals C2, and
Column A is greater than D2.

2. Will this work if my sheet names have spaces?

Yes. It works perfectly even if your sheet names contain spaces (like “April 23”), because the INDIRECT reference automatically encloses the name in quotes through r&"!range".

3. What if a sheet name in the list doesn’t exist?

If any sheet listed in A2:A doesn’t exist, that part of the formula may return an #N/A error.
To prevent this, wrap your SUMIF with IFNA, like so:

=SUM(MAP(A2:A4, LAMBDA(r, IFNA(SUMIF(INDIRECT(r&"!B2:B"), C2, INDIRECT(r&"!C2:C")), 0))))

This way, missing sheets will simply be treated as zero, avoiding formula breaks.

(Note: I didn’t include IFNA in the main formula so that you can easily troubleshoot sheet name errors if needed.)