• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel 3D SUMIF Across Multiple Worksheets

You are here: Home / Excel Formulas / Excel 3D SUMIF Across Multiple Worksheets
Excel 3D SUMIF Across Multiple Worksheets
May 2, 2012 by Mynda Treacy

No, no, no, no, no I’m not talking about the latest 3D animated movie.

I’m talking about how you can use SUMPRODUCT with SUMIF and INDIRECT to conditionally summarise data on multiple worksheets, for example when you’re creating a summary sheet in your workbook.

First the data:

I’ve got 12 sheets just like the one below, one for every month – see the tabs at the bottom.

Excel 3d SUMIF multiple sheets

And I’ve got my summary sheet that totals up the data for each builder by region:

Excel 3d SUMIF multiple sheets

Here’s the problem (read ‘fun challenge’)

1. The data for each month contains multiple entries for some builders, so I can't simply sum it.
2. You can’t use the SUMIFS function across multiple sheets…well not on its own.

Solution 1: The slow option

If you’ve got oodles of time and a super computer you could add one SUMIF to another in one massive long formula like this:

=SUMIF(Jan!$A$3:$A$8,Summary!A5,Jan!B3:B8)+SUMIF(Feb!$A$3:$A$8, Summary!A5,Feb!B3:B8)+SUMIF……

Or.

Solution 2: The fast option

You can use a formula like this (from cell B5 of the summary sheet):

=SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!A3:A8"),$A5,INDIRECT("'"&tabs&"'!B3:B8")))

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.


Download the workbook used in this example
. Note: The workbook download is a .xlsx file. If your browser changes the file extension to a .zip or .xml then you will need to replace it with .xlsx before saving the file.

How this formula works

I’m not going to go into massive detail as you can read up on SUMPRODUCT here, SUMIF here and INDIRECT here but I will to point out a couple of things.

If you know how to use SUMIF then you will recognise that cell A5 contains your criteria, in this case the Builder’s name.

The other thing to note is the reference ‘tabs’. This is the named range given to the list of my sheet tab names located in cells G2:G13:

Excel 3d SUMIF multiple sheets

Note: you don’t need to give your list of sheet tabs a named range. You could simply reference the cells like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$13&"'!A3:A8"), $A5,INDIRECT("'"&$G$2:$G$13&"'!B3:B8")))

Back to why we need to create a list of sheet tabs…listing out the sheet tab names enables you to use the INDIRECT function to create the reference to each sheet on the fly, which results in a short elegant formula.

The Bad News

Because sometimes we have workbooks that contain a crazy number of sheets with complicated names you might be daunted by the idea of creating a list of the sheet names.

The Good News

Next week I’ll show you how to extract a list of your sheet tab names with some dead easy VBA. Seriously, it’s copy and paste kind of stuff.

Excel 3D SUMIF Across Multiple Worksheets

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:Force Excel to Open on a Specific SheetForce Excel to Open on a Specific Sheet
Next Post:List Sheet Tab Names with Excel VBAlist sheet tab names with vba

Reader Interactions

Comments

  1. carlos figueroa

    July 12, 2022 at 1:30 am

    I have information in different sheets and I need to have a summary , for samples , if I choose one day of the week , the resume show me the information that I have in the sheet 1 ,if I choose second day of the week the resume show me the information that I have in the sheet 2 .

    How I can make the formula with 3D referent??

    Reply
    • Mynda Treacy

      July 12, 2022 at 9:40 am

      Hi Carlos,

      The best tool for this is a PivotTable and Slicers, but you would have to consolidate your data onto a single sheet, rather than having it spread across multiple sheets. You can use Power Query to consolidate data across multiple sheets.

      Mynda

      Reply
  2. Julian Chen

    December 19, 2021 at 1:19 pm

    I have a formula to summarize the reference across multiple worksheets as below:
    SUM(‘Sheet1:Sheet5’!F352)
    In case I would turn the reference “F352” to a dynamic range like “F”&TODAY()-44195, how could I put the assembled text “F”&TODAY()-44195 in the 3-D formula to replace F352. Thank you MYNDA I count on your brilliant guidance.

    Reply
    • Catalin Bombea

      December 19, 2021 at 2:45 pm

      Hi Julian,
      To create a range from text pieces, use the INDIRECT function, this returns a range:
      =INDIRECT(“F” & TODAY()-44195)

      Reply
      • julian

        December 20, 2021 at 8:56 am

        My question is how to embed in the formula SUM(‘Sheet1:Sheet5’!F352)
        to substitute” F352″, I tried to assign that INDIRECT function to a defined name “POS” then put it in the SUM function as follows:
        SUM(‘Sheet1:Sheet5’!&POS)
        but it didn’t work . I guess it must be incorrect join text and believe you can solve it. So, I come here!

        Reply
        • Catalin Bombea

          December 20, 2021 at 1:59 pm

          SUM function needs a range, INDIRECT can return a range from text.
          INDIRECT will not be able to create a 3D reference, so your sheets will have to follow a name convention:
          =SUMPRODUCT(SUMIF(INDIRECT(“‘Sheet”& {1,2,3,4,5}& “‘!F” & TODAY()-44540),”<>0″))
          This formula will sum Sheet1:Sheet5

          Reply
          • julian

            December 20, 2021 at 11:51 pm

            Hi Catalin,

            Many thanks to you. whether it be VBA or Function you alway come to my rescue with amazing solutions. I do appreciate it.

            Br,

            Julian

          • Catalin Bombea

            December 21, 2021 at 1:08 am

            You’re welcome, glad to hear it works!
            Cheers,
            Catalin

  3. andi

    March 15, 2021 at 12:13 pm

    SUMIF(‘1:10′!A:A,B4,’1:10’!B:B) kenapa nilainya Value

    Reply
  4. Jared

    December 15, 2020 at 7:25 am

    This was absolutely amazing and helped me so much, thank you! However, I am running into a problem with merged cells. The amount I need to sum is based on an account number (only one per sheet) but my criteria ranges are based off of an exception type and number of days past due (there could be multiple per account). If there are multiple exceptions for the account, the amount is only listed in the first row for that group of exceptions for the account and the formula does not pick up the amount if it is not on the same line. Is it possible to get the amount I need from a merged cell?

    Reply
    • Mynda Treacy

      December 15, 2020 at 7:58 am

      Hi Jared, glad you found this helpful. Merged cells are evil and should never be used for this very reason 🙂 I recommend you remove the merge formatting and enter the value in every cell. This is the correct way to structure your data. In fact, you really shouldn’t have your data spread across multiple sheets. The correct data format is tabular. I hope that points you in the right direction.

      Reply
      • Jared

        December 16, 2020 at 12:08 am

        I agree! The problem is the Excel file is coming from Cognos and needs to be grouped by account and separated by lender on different sheets. They now want a summary for all lenders on a new sheet. If it wasn’t for the grouped/merged cell I’d be really happy. I did the same for counting items and it worked perfectly. I guess I’m going through 36 sheets and manually un-merging cells 🙂

        Reply
        • Catalin Bombea

          December 16, 2020 at 4:14 am

          Hi Jared,
          You can also try Power Query if you have excel 2010+, to combine all sheets for reports.

          Reply
  5. Angelo

    September 17, 2020 at 10:52 am

    After working on this problem for 5 hours and attempting so many longer versions, your recommendations help me get it done in 2 minutes. Wish your link was first to pop up on a google sheet for sumif multiple sheets.

    Reply
    • Mynda Treacy

      September 17, 2020 at 2:17 pm

      So pleased we could help, Angelo! Just a shame you didn’t find us sooner. You can help our link appear higher up the search results by sharing this page 😉

      Reply
  6. Rachel Medina

    March 13, 2020 at 6:28 pm

    I have a variation on this example that I can’t figure out. I have a year of data for each sales person in a by month format , so each sales person’s sales is on a separate worksheet with types of sales down column A and the months across the top – Jan 2019 Feb 2019 Mar 2019 etc. I need to consolidate the data on a summary sheet by sales (rows) and months (columns) across the top. My problem is that the months on each tab are in different columns. I could do this with sumif/index /match if the data were on a single sheet, but it doesn’t work using the 3D function using a table of worksheet names. I can send and example if not clear. Thanks for any insight.

    Reply
    • Mynda Treacy

      March 14, 2020 at 11:15 am

      Hi Rachel,

      This is a classic case of your data not being in the right layout and it’s causing you all sorts of problems now that you want to summarise it. Ideally your data should be in a tabular layout. This will allow you to use PivotTables or SUMIFS to easily summarise the data.

      You can use Power Query to unpivot the data and then consolidate it into a tabular layout.

      If you get stuck, please post your question on our Excel forum where you can upload a sample Excel file and we can help you further.

      Mynda

      Reply
  7. Anakowi Takase

    September 21, 2019 at 1:14 pm

    I’ve tried various approaches to 3D but can’t nail exactly what I want, which is to SUM the total HOURS spent at a SITE for a MONTH across 3 sheets (one for each contractor).
    Most examples assume you have a different sheet for each month.

    Main data source: Col.A = dates . Col.B = sites . Col.C = hours
    Summary: Col.D = months
    Columns E to K formula (each Site): total-hours-byMonth (E1=SiteName).

    If I’m doing this on the individual contractor-sheet, the working formula looks like this:
    =SUMIFS(C3:C10,A3:A10,”>=”&$D3,A3:A10,”<="&EOMONTH($D3,0),B3:B10,E$1)

    Will it be possible to create a summary sheet?
    =SUMPRODUCT(SUMIF(INDIRECT(…… etc.

    Reply
    • Mynda Treacy

      September 22, 2019 at 9:40 am

      Hi Anakowi,

      I recommend you use Power Query to get the data from each of the sheets and consolidate it into one table so you can use SUMIFS the way it was intended, or even easier, summarise it with a PivotTable. If you really want to use a formula, then please post your question on our Excel Forum with your sample Excel file where we can help you further.

      Mynda

      Reply
  8. Lloyd

    September 14, 2018 at 10:09 pm

    Hi,

    I hope someone can help. I’ve adapted the formula to the below but the result if #REF!

    Can someone advise what I am doing wrong? I get confused with the use of ” and ‘ so any explanation would be helpful.

    =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs2&”‘!A5:A535”),$A6,INDIRECT(“‘”&tabs2&”‘!DY5:DY535”)))

    Thanks in advance,

    Lloyd

    Reply
    • Mynda Treacy

      September 15, 2018 at 2:28 pm

      Hi Lloyd,

      On the face of it the formula looks ok. We’d have to see the workbook to troubleshoot further. Please post your question and Excel file on our forum.

      Mynda

      Reply
  9. MITUL PARIKH

    August 18, 2018 at 9:32 am

    Hi; Mynda;

    I learned lot and enjoyed this article & other 2 articles. Thank you for writing so nicely.

    Reply
    • Mynda Treacy

      August 18, 2018 at 6:42 pm

      Glad it was helpful, Mitul.

      Reply
  10. Aubrey

    August 7, 2018 at 6:41 am

    Hello,

    I need help with the following requirement. I have multiple tabs and a summary sheet at the beginning. I am using the formula provided here to sum across the tabs on the summary sheet. However, new sheets are going to constantly be added to this workbook so the named range needs to automatically update as a new sheet is added. How can this be accomplished?

    Reply
    • Catalin Bombea

      August 8, 2018 at 1:49 pm

      Hi Aubrey,
      You can use this formula for the named range, it is dynamic:
      =$G$2:INDEX($G$2:$G$500,COUNTA($G$2:$G$500))

      Reply
  11. Julianne

    June 28, 2018 at 1:34 pm

    I have been using the formula and have made a ‘named range’ for my worksheet names called ‘JuneNW’ (equivalent to your ‘tabs’). However, The formula is only using the value from the first worksheet (out of 17 sheets) instead of adding up the values associated with my ‘criteria’. Not sure if it matters that I am on google sheets instead of Microsoft excel. I have checked my formula countless times and it is identical to the one given above.

    Reply
    • Catalin Bombea

      June 28, 2018 at 7:47 pm

      Hi Julianne,
      We cannot help with Google sheets, the formula is designed for Excel, no idea if it works exactly the same on google sheets. You can try the formula in excel, you will have a comparison of the same file in excel and google sheets.
      Catalin

      Reply
  12. Ishwar Singh

    April 19, 2018 at 1:45 pm

    Very useful formula. Thanks.

    Reply
  13. adrin

    March 23, 2018 at 11:22 pm

    Hai guys.. I had experiencing difficulty in using formula sumproduct + sumif + indirect in calculating only “Actual” Total Sale on each sheet. Imagine that anybody can give a formula for the actual Total Sales data summarized into the Summay sheet. Examples of data on each sheet are as below.

    Sheet1
    Division Type Sales Retail
    Division 11-Total Budget 792,510.00
    Division 11-Total Actual 764,150.28
    Division 12-Total Budget 1,358,200.00
    Division 12-Total Actual 1,514,622.54
    Division 13-Total Budget 1,322,000.00
    Division 13-Total Actual 1,084,751.09
    Division 14-Total Budget 49,000.00
    Division 14-Total Actual 21,093.32

    Sheet2
    Division Type Sales Retail
    Division 11-Total Budget 296,000.00
    Division 11-Total Actual 265,594.15
    Division 12-Total Budget 416,000.00
    Division 12-Total Actual 305,710.21
    Division 13-Total Budget 440,100.00
    Division 13-Total Actual 527,700.01
    Division 14-Total Budget 29,000.00
    Division 14-Total Actual 11,126.42

    Sheet3
    Division Type Sales Retail
    Division 11-Total Budget 473,000.00
    Division 11-Total Actual 335,234.91
    Division 12-Total Budget 394,000.00
    Division 12-Total Actual 374,468.35
    Division 13-Total Budget 829,154.00
    Division 13-Total Actual 649,062.53
    Division 14-Total Budget 56,000.00
    Division 14-Total Actual 11,330.83

    Sheet4 (Summary)
    Division Total Sale(Actual) Total Budget
    Division 11-Total
    Division 12-Total
    Division 13-Total
    Division 14-Total

    Reply
    • Catalin Bombea

      March 24, 2018 at 2:55 am

      Hi Adrin,
      Can you please upload a sample file on our forum? It will be easier for us to provide a functional formula and test it on your data structure.
      Regards,
      Catalin

      Reply
      • azri adrin

        March 26, 2018 at 1:09 am

        Hai Catalin.. the sample file had been uploaded.. Topic name is “Need sum Sale (Actual) from multiple sheet”.

        Hopefully getting feedback from you soon..

        Thanks..

        Reply
        • Catalin Bombea

          March 26, 2018 at 1:57 pm

          Looks like you already got a functional formula 🙂
          Regards,
          Catalin

          Reply
  14. Simon Preston

    September 6, 2017 at 10:19 pm

    Awesome! Thank you.

    Reply
  15. Daniel Deak

    June 2, 2017 at 11:31 pm

    Hi! Could you help me with this modification?

    In the function above can you use a criteria range instead of a single cell criteria?

    like change the criteria in the function above from $A5 to A$5:A$9 or something

    I have the same problem, but i want to sum based on multiple criteria. It seems to work on a single sheet but when i try to apply it in this formula it’s not working.

    this is how i try:

    =SUMPRODUCT(SUMIF(INDIRECT(“‘”&$G$2:$G$13&”‘!A3:A8”), $A5:$A9,INDIRECT(“‘”&$G$2:$G$13&”‘!B3:B8”))).

    If i give a big criteria range it gives back “NA” error if i try a shorter range it gives back false numbers.

    Thanks for the help!

    Reply
    • Catalin Bombea

      June 5, 2017 at 12:31 am

      Hi Daniel,
      Can you upload a sample file so we can see what’s wrong? Use our forum to create a new topic and upload a file.
      Catalin

      Reply
  16. michaele

    June 1, 2017 at 5:44 am

    This is great! now, how can I apply this concept using the INDEX formula so that my formula be more dynamic?

    Reply
    • Mynda Treacy

      June 1, 2017 at 10:39 am

      Hi Michaele,

      Replace the named range ‘tabs’ with a dynamic named range.

      Mynda

      Reply
      • Michaele

        June 2, 2017 at 7:46 am

        Thanks for the quick response. Maybe I didn’t frame my question right. I like the named range in the example above. And after using the formula more, and finding the ‘address’ function in comments/replys below to make it so the formula can be dragged over and the column update, I don’t need to use the INDEX formula. However, the example given below

        (=SUMIF(INDIRECT(“‘”&$D$27&”‘!$B$1:$B$368″),INDIRECT($D$27&”!$A$375″),INDIRECT(“‘”&$D$27&”‘!”&ADDRESS(1,COLUMN(C1),2)&”:”&ADDRESS(368,COLUMN(C1),2)))

        Doesn’t play well when a line gets inserted on the underlying tabs (it doesn’t update the range reference). Any thoughts?

        Reply
        • Michaele

          June 2, 2017 at 7:54 am

          Actually perhaps describing my use might be more helpful:

          I am building a 10yr forecast model whereby there are underlying sheets (financial departments), which need to aggregate/roll up. I am familiar with INDEX combined with MATCH to pull back one value, but have not used it to pull back or aggregate multiple values from multiple sheets. Essentially that is what I am looking to do and I stumbled upon the above as a way to sum multiple tabs based on criteria.

          Reply
          • Mynda Treacy

            June 2, 2017 at 8:44 am

            In that case I would stop right there before it’s too late to fix the layout of your model. You should never spread source data over multiple sheets. Your source data should always be in a tabular format. This way you don’t have to wrangle Excel formulas to do things they weren’t designed for. Writing unnecessarily complicated formulas like this will only undermine the robustness of your model.

            When your source data is in a tabular format you can easily use PivotTables to extract subsets of your data, or SUMIFS formulas to recreate those individual sheets, if that’s the final view you want.

            Mynda

  17. Bhawna Joshi

    April 26, 2017 at 5:28 pm

    =SUMIF(INDIRECT(“‘”&$D$27&”‘!$B$1:$B$368″),INDIRECT($D$27&”!$A$375″),INDIRECT(“‘”&$D$27&”‘!C$1:C$368”))

    In this formula if I want C$1:C$368 to change as per column(when dragging) , how to do that. Please help

    Reply
    • Catalin Bombea

      April 26, 2017 at 6:03 pm

      Try this version:

      =SUMIF(INDIRECT("'"&$D$27&"'!$B$1:$B$368"),INDIRECT($D$27&"!$A$375"),INDIRECT("'"&$D$27&"'!"&ADDRESS(1,COLUMN(C1),2)&":"&ADDRESS(368,COLUMN(C1),2)))
      Reply
  18. Soren

    December 29, 2016 at 12:42 am

    Great tutorial.

    I have made a formula to extract the tab names (to avoid VBA)
    =RIGHT(CELL(“filename”;A1);LEN(CELL(“filename”;A1))-FIND(“]”;CELL(“filename”;A1)))

    Then it is easy to refer to the tab names with a formula and changing the name of a tab will not cause the SUM IF formula to fail.

    Reply
    • Mynda Treacy

      December 29, 2016 at 8:32 am

      Nice tip, Soren. Thanks for sharing.

      Mynda

      Reply
  19. Fred

    June 7, 2016 at 6:40 pm

    I have few sheets within a workbook all having the same format.
    Each sheet has Row 1 with B1..AF1 as date example from 1-May to 31-May
    Column A2…A10 are various types of component names.
    All sheets again uses the same date row and column name description.
    We assume sheet 1 to sheet 3.
    I want to create a summary page with Column description the same, but I just need to key into the cells selected as the following :
    Sheet Name : just enter SHEET 3
    Start Date: just enter 1-May
    End Date: just enter 2-May

    Once I key in the Sheet name with the date range selected, the Column will automatically show me all the total value of the quantity within the date range selected.

    Can you help me on this?

    Reply
    • Catalin Bombea

      June 8, 2016 at 2:06 pm

      Hi Fred,
      Can you please upload a sample file to see your data structure? You can use our Help Desk (create a new ticket), it will be easier to provide a personalized answer, to match your structure.
      Catalin

      Reply
      • Fred

        June 8, 2016 at 3:39 pm

        Hi Catalin,
        Thk u for the prompt reply. I have already send my sample through the new ticket.
        Regards
        Fred

        Reply
  20. David Briggs

    March 30, 2016 at 6:27 am

    Great Formula!
    Can I assume that the INDIRECT function is generating a multidimensional array that ultimately gets resolved by the SumProduct function? I tried using F9 to evaluate pieces of the formula but it doesn’t allow me to see the entire array.

    Thanks

    Reply
    • Catalin Bombea

      March 30, 2016 at 3:20 pm

      Yes, you got it right, INDIRECT will create a range from that text string.
      If you select the entire argument of the INDIRECT function "'"&tabs&"'!A3:A8" and press F9, excel will show the ranges created:
      {“‘Jan’!A3:A8″;”‘Feb’!A3:A8″;”‘Mar’!A3:A8″;”‘Apr’!A3:A8″;”‘May’!A3:A8″;”‘Jun’!A3:A8″;”‘Jul’!A3:A8″;”‘Aug’!A3:A8″;”‘Sep’!A3:A8″;”‘Oct’!A3:A8″;”‘Nov’!A3:A8″;”‘Dec’!A3:A8”}
      If you select the entire function, INDIRECT("'"&tabs&"'!A3:A8") and press F9, excel will give you the content of those ranges:
      {“Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”}
      If the range is too large, excel fails to display the data, there is a limit in the length of the text displayed.
      Cheers,
      Catalin

      Reply
  21. John Galich

    January 14, 2016 at 3:06 am

    Hi,

    This site is a great find and a gold mine of information. This process is very, very close to helping me solve what I want to do, but not quite.

    I have a 12 tabs in a spreadsheet (one for every month) plus a summary/year-to-date (YTD) sheet where we track the sales of each of our 23 reps for three core product categories. Each month and rep also has a forecast for each core product category already built into each month tab.

    What I want to do is a YTD “SUM IF” of the forecast fields in the Summary tab based on the month being processed. For example, if this is May 1, I would want to sum the forecasts for January through April only. Any suggestions are welcome.

    Thank you!

    John Galich.

    Reply
    • Catalin Bombea

      January 15, 2016 at 3:39 am

      Hi John,
      Can you please upload a sample file with your data structure on our Help Desk? (create a new ticket). It will be easier to provide a personalized answer.
      Cheers,
      Catalin

      Reply
  22. jas

    January 8, 2016 at 7:08 am

    Hi guys,

    I am just starting with Excel, I know how to use formulas like sumif, indirect or sumproduct. But all of them by its own. I am trying to solve the example. As I am trying to really understand what I am doing, how can i use this kind of symbols, without just copying the formula that you give me?

    ”
    “‘”
    &
    Thank you very much

    Reply
    • Catalin Bombea

      January 8, 2016 at 1:21 pm

      Hi,
      The single quotes (or apostrophy) are used in sheet names references, if the sheet name contains spaces , like this simple reference to a cell in another sheet:
      =’Test Sheet’!B4 . If the sheet name does not contain spaces, the apostrophies are not needed: =Sheet1!A1
      The double quotes are used in text strings, like: =”John Doe”
      The & simbol is used for joining together multiple pieces of text, like:
      =”John “&”Doe”
      You will get the same result by using the CONCATENATE function: =CONCATENATE(“John “,”Doe”)
      Hope this clarifies some things 🙂
      Cheers,
      Catalin

      Reply
      • jas

        January 19, 2016 at 12:09 am

        Thank you!

        Reply
      • jas

        January 19, 2016 at 12:22 am

        Just one thing. Like the example we are using:
        …INDIRECT(“‘”&$G$2:$G$13&”‘!A3:A8″…

        What I understand is:

        “‘”= reference text which means nothing, no?, and
        range G2:G13 and

        “‘!A3:A8” another text reference with only one single quote

        Reply
        • Catalin Bombea

          January 19, 2016 at 2:36 am

          Hi,
          A reference to a range from another worksheet looks like this:
          =’Sheet 1′!A3:A8
          Note that only the worksheet name is found between single quotes, before the exclamation mark.
          In INDIRECT function, we recreate this reference structure by joining together multiple pieces of text strings:
          =INDIRECT(“‘” & G2:G13 & “‘!” & “A3:A8”)
          G2:G13 must hold the worksheet names list, as you can see we have to add a single quote before and after the worksheet names, folowed by the exclamation mark and range reference.
          Cheers,
          Catalin

          Reply
  23. Isabella

    October 17, 2015 at 1:42 am

    Hi,

    I have a worksheet and I was able to use this function perfectly. I just have an issue for some data that have codes within them. So for example in tab Jan instead of having Doug, I have WAVE_1 (Doug), and then for February it would be something like WAVE_2 (Doug), is there a way to use index match to find content within the cell instead of match the cell value?

    Thank you,

    Isabella

    Reply
    • Mynda Treacy

      October 18, 2015 at 8:38 am

      Hi Isabella,

      Yes, probably, but I’d be inclined to separate the name out of the cells rather than try and coerce a formula into handling it.

      You can use Text to Columns to extract the name in a few clicks.

      Kind regards,

      Mynda

      Reply
  24. Winnie

    June 11, 2014 at 12:37 pm

    Hi there,

    Just wondering if this formula works if the ‘Dougs’ on Jan-Dec worksheets are in different cells.

    So far, all Dougs are in cell A3.
    What if:
    Jan: Cell A3
    Feb: Cell A16
    Mar: Cell A9

    Thank you!

    Reply
    • Mynda Treacy

      June 12, 2014 at 7:54 pm

      Hi Winnie,

      It will work if the ‘Dougs’ are in different cells in column A on each sheet.

      Item 1 under the heading “Here’s the problem (read ‘fun challenge’)” says”

      “The data for each month contains multiple entries for some builders, so I can’t simply sum it.” So therefore if you have your matching name in a different cell in column A it will work.

      You just need to alter the range to include all the possible rows e.g. instead of A3:A8 make it to at least A3:A16, since your example says ‘Feb’ data is in cell A16. Likewise for the column B range. The formula below allows for up to row 100:

      =SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!A3:A100"),$A5,INDIRECT("'"&tabs&"'!B3:B100")))

      Kind regards,

      Mynda

      Reply
      • André

        June 24, 2015 at 5:14 pm

        Hi Mynda,

        Thank you for this useful formula
        Can you please tell me if there is a possibility to use this formula if central, east, north & South on Jan-Dec worksheets are in different cells.

        Thank you
        André

        Reply
        • Mynda Treacy

          June 24, 2015 at 7:18 pm

          Hi André,

          No, it requires the region to be in the same column.

          Mynda

          Reply
  25. Chee Teik Ong

    September 6, 2013 at 1:42 pm

    Hi Mynda,

    This is a very interesting excel trick! But what is the purpose of having SUMPRODUCT in the formula? I know that the formula will not work without it, but can’t really figure out the reason behind it.

    Can you please explain?

    Thanks.

    Reply
    • Mynda Treacy

      September 6, 2013 at 2:01 pm

      Hi Chee Teik,

      SUMIF on its own can’t handle a 3D reference, but if you pass the resultant array to SUMPRODUCT it can sum up the values.

      Kind regards,

      Mynda.

      Reply
      • Chee Teik Ong

        September 7, 2013 at 10:50 am

        Thanks Mynda, this really opens my eyes- SUMPRODUCT 3D.

        Reply
  26. MF W

    June 19, 2013 at 4:45 pm

    I like your trick in creating the ‘tabs’ with INDIRECT. Nice! 🙂

    Regarding your example, I would actually prefer Data Consolidation to setting formulas, given that the data in different worksheets are static.

    What do you think?

    Reply
    • Carlo Estopia

      June 19, 2013 at 5:46 pm

      Hi MF W,

      Yes, you’re right. It’s different strokes for different folks.

      However, I have my opinion that it’s easier to add one more item–builder in this case– and
      in case of an additional sheet by just adding one more sheet name to the ‘tab’ range, and then just
      copying the formulas without much ado to the new item(builder) to get the same results.
      I think it’s harder — by a teenie-weenie only –for me to redo the formulas
      to a new item(builder) using Data Consolidation in this case especially with an additional
      sheet. But of course this is just my opinion and very negligible. It doesn’t really matter which you choose.

      It’s just a matter on what you’re used to. It’s just like some people would prefer
      keyboard shortcuts to a mouse.

      Cheers,

      CarloE

      Reply
  27. Mark

    June 7, 2013 at 12:53 am

    Excellent expaination of a very complex issue – thank you, that was very helpful.

    Reply
    • Mynda Treacy

      June 7, 2013 at 10:46 am

      Cheers, Mark 🙂

      Reply
  28. diogo

    May 6, 2013 at 11:58 pm

    doesn’t work with NA() is cell… any alternative to sum cross sheets with NA() ?

    Reply
    • Mynda Treacy

      May 7, 2013 at 8:50 pm

      Hi Diogo,

      Get rid of the NA errors by wrapping the formulas in an IFERROR function.

      Kind regards,

      Mynda.

      Reply
  29. Andrew

    April 25, 2013 at 10:18 pm

    Hi,

    How would you alter the formula so that instead of just typing in D2 and checking for one name in one cell across all worksheets (ie. Doug), it checked and matched two names in two adjacent cells (ie Doug Brown in D2:D3)

    Reply
    • Carlo Estopia

      April 26, 2013 at 10:02 am

      Hi Andrew,

      First of all, I hope we are talking about the same file here: Excel_Blog_3d_countifs.

      I don’t think it’s possible. SUMIF or even SUMIFS is designed only to evaluate a criteria range only once. For example,
      If the ‘Builder’ column is evaluated as you suggested once for Doug and once for Brown, SUMIF could not perform an ‘Or’ mechanism rather if the same range
      is to be evaluated twice it will not result into anything because it will require that both ‘Brown and Doug’ criteria be satisfied and not either.

      I hope it’s clear because I am not good at explaining 🙂

      Cheers,

      CarloE

      Reply
  30. stonecottage

    April 20, 2013 at 12:41 am

    I need to track multiple tabs for both a date range and a name range. Working in one workbook, I have a tracking tab to summarize and the tab names are years (say 2002- 2013). I wanted to use the INDIRECT formula but am running into a road block. If I have a dynamic name range indicating a list of tab names. Should I be putting a name range on each tab as well? I have had to use the actual tab name to track but only am getting half way there. I can track date ranges from each tab but not name and date ranges together. (the list of dates and names also exist on the tracking tab for an easier formula.) Heres what I have so far.

    =COUNTIFS(‘2002′!$M$3:$M$12081,”>=”&E$17,’2002’!$M$3:$M$12081,”<="&E$18)

    Can you help?

    Reply
    • Carlo Estopia

      April 22, 2013 at 7:04 pm

      Hi stonecottage,

      Please send this file here: HELP DESK.

      Please explain your problem further.

      Cheers,

      CarloE

      Reply
  31. Tobias Ford

    April 3, 2013 at 1:06 pm

    Hi there Mynda,

    I’m having a problem with a workbook that i am working on and here is my scenario.

    I have a range of tables across multiple sheets that all reflect safety stats. Each sheet has the same layout and structure and applies to different site locations. Each sheet is accompanied by a corresponding ‘graphical’ sheet which illustrates the stats in a graph.

    What i am idealy attempting to do is to create a summary page using the same structure and tables as the site pages, yet summing pertinent values from the various sites.
    EG hours worked is B Column and the first value occurs in B3.

    I’m wanting to SUM the vaules from all the relevant site pages into the summary page, whilst excluding the graph pages.

    Additionally i’m wanting the formula to have drag down functionality.

    I was thinking that sumproduct or the sumifs would be a good solution – yet a lot of the syntax here is going over my head.

    Any help with a solution would be much appreciated
    Kind regards

    Tobias Ford

    Reply
    • Carlo Estopia

      April 6, 2013 at 11:28 pm

      Hi Tobias,

      Please send this through HELP DESK.

      It would be easier if we can see this on file.
      Otherwise we can only advise to go and read our posts
      –and you guessed it right–:

      SUMPRODUCT
      SUMIFS

      Cheers,

      CarloE

      Reply
  32. MIHAELA TOMESCU

    April 2, 2013 at 3:21 am

    PERFECT!!!! THANK YOU!!! YOU SAVED ME!

    Reply
    • Carlo Estopia

      April 3, 2013 at 12:21 am

      Hi Mihaela,

      On behalf of Mynda,
      You’re welcome!!!

      Carlo

      Reply
  33. Berry Wilson

    March 12, 2013 at 1:04 am

    Looks very good

    Reply
    • Mynda Treacy

      March 12, 2013 at 1:39 pm

      Cheers, Berry 🙂

      Reply
  34. stephanie

    February 4, 2013 at 6:31 am

    I have studied the functions used in this and I don’t understand why you use sumproduct AND sumif, why can’t you use sumproduct and indirect on their own?

    Reply
    • Carlo Estopia

      February 4, 2013 at 9:48 am

      Hi Stephanie,

      Sumproduct can be use on its own with INDIRECT. However, for some weird reasons, It doesn’t work like the SUMPRODUCT,SUMIF,INDIRECT combined.
      In short, Sumproduct can only use a 1-celled dynamic range. For example, In our article, We cannot use the ‘tabs’ range which has 12 months(data in it).

      If you have downloaded the file already,
      Try this variation in E5.
      =SUMPRODUCT(INDIRECT(“‘”&tabs&”‘!E3:E8”)*(INDIRECT(“‘”&tabs&”‘!A3:A8”)=$A5))

      you’ll get an ERROR. lol. but that’s not it.
      Goto Formulas Ribbon, Name Manager, CLick ‘tabs’ and edit its range to just $G$2.
      you’ll see that your error changes to 3 which is the value of Doug for the Jan sheet.

      To be honest, I don’t know why it’s like this. I guess, SUMPRODUCT is way too complicated
      a function that its creator just wanted to limit its range. Its complication is that you
      can use +(OR) and *(AND) operators in a very diverse kind of way. Incidentally, SUMIFS will work
      too despite its multiple criteria ranges/criteria feature. I guess -again- it’s more
      predictable unlike SUMPRODUCT. Who knows someday they’ll improve this limitation.

      Sincerely,

      CarloE

      Reply
  35. Rashid Mahmood

    January 26, 2013 at 7:31 pm

    SUMIF FORMULA is =SUMPRODUCT(SUMIF(INDIRECT(“‘”&A2&”‘!O:O”), B2,INDIRECT(“‘”&A2&”‘!AE:AE”)))

    Where
    1 A2=SHEET NAME
    2 O:O= CRITERIA RANGE
    3 B2=CRITERIA
    4 AE=SUM RANGE

    Reply
    • Mynda Treacy

      January 27, 2013 at 1:11 pm

      Hi Rashid,

      Do you have a question or are you just making a statement?

      Cheers,

      Mynda.

      Reply
  36. Rob

    January 21, 2013 at 7:28 pm

    Hello Mynda,

    I’ve followed your advice, created a group named ‘tabs’ and listed all of the tab names in it.

    I’m using several summary sheets in which I am adding wins and losses in the following groups: Overall, Conference, and Division. Your formula works perfectly for Overall and Conference wins and losses (which are in separate columns, i.e. Overall wins, Overall losses, Conference wins, Conference losses).

    For Division wins and losses, my reference range is different, where instead of referencing a range of 12 cells, I’m using a range of six cells (also on the same spreadsheets). For some reason, the totals are not adding up for this group and I can’t figure out what’s different.

    For the North Division wins, my formula is: =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!V2:V7”),$A4,INDIRECT(“‘”&tabs&”‘!P3:P14”)))

    While for the South Division wins, the formula is: =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!V9:V14”),$A4,INDIRECT(“‘”&tabs&”‘!P3:P14”)))

    V2:V7 & V9:V14 reference a spot on the ‘tabs’ worksheet where I have division standings. The ranges are correct, but the totals aren’t — that is if the team has a total >0. It is totalling ‘zero’ just fine.

    Can you help?

    Reply
    • Carlo Estopia

      January 23, 2013 at 4:52 pm

      Hi Rob,

      I have tried some experiment on my own but It would be better if you send me the file through Help Desk so I can see the flow or logic of your formulas and the data it is based upon.

      Sincerely,

      CarloE

      Reply
      • Rob

        January 24, 2013 at 3:27 pm

        Hi Carlo,

        Thank you! I’ve submitted a ticket and attached my file.

        Reply
        • Carlo Estopia

          January 24, 2013 at 6:43 pm

          Hi Rob,

          I was quite lost there for awhile. 😉
          Anyway, you said that your range V2:V9 is correct, but I beg to disagree because you’re using a SUMIF.
          SUMIF works only if the rows of the sum range and the range criteria are of the same dimension.
          in your case V2:V7 being the criteria could not locate P3:P14 except I guess between P3:P7 which would
          qualify as of the same dimension–that is why I think It picked up several figures.

          =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!H3:H14”),$A4,INDIRECT(“‘”&tabs&”‘!P3:P14”)))

          So that solves why your SUMIF isn’t working.

          As to the expected, next problem…

          I suggest — it’s just a suggestion– that you have a master list as to who’s in ACC North and who’s in ACC South. In that way we can easily put teams in the right division using an IF Function directly in your ACC Sheet… i.e. IF Virginia is North then our SUMPRODUCT function else 0. something like it.
          I am saying this because I have tried to use the SUMPRODUCT using a combo of INDIRECT, ISNA,ISERROR,VLOOKUP and it just provided an inconsistent result… and I don’t know why.

          Sincerely,

          CarloE

          Reply
          • Rob

            January 28, 2013 at 5:08 am

            Hi Carlo,

            Thank you for looking into this for me. I didn’t consider the restrictions in range and your explanation makes perfect sense.

            If I can figure out how to look up a value in ranges x and y, then pull the information from range z, do this for a range of tabs adn sum the figures, then take that corresponding value and put it in either section a or b (depending on where finding the value(s) in x or y) of the summary sheet, I’ll be all set.

          • Carlo Estopia

            January 28, 2013 at 2:59 pm

            Hi Rob,

            Do you still need help on that? I’ve been doing something like that last week and I got inconsistent results. I think I can do it on a sheet per sheet basis but not on a range of tabs using INDIRECT.

            I will still try though. Do you like a customized VBA function?

            I will send it to you through helpdesk.

            Sincerely,

            CarloE

          • Nick C

            March 26, 2013 at 8:34 pm

            Your formula =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!E18:E190”),$H8,INDIRECT(“‘”&tabs&”‘!g18:g190”)))

            Works brilliantly for me but i need to amend slightly so that the formula counts the number of items in G18:G190 rather than sums them up. Replacing SUMIF with COUNTIF unfortunately doesnt work.
            Also I need to amend the formula after that to count the number of items that are in I18:I190 (instead of G18:G190) that have the word “Bound” in it.

            Any thoughts?

          • Carlo Estopia

            March 26, 2013 at 10:13 pm

            Hi Nick C,

            I am sure I have answered a similar question before and I thought it was you but
            I couldn’t find the thread.lol

            Anyways, Nick try to position your cursor in your current formula and “CTRL+SHIFT+ENTER”
            that will make an array formula of countif.

            Read : Array Formulas

            Cheers.

            CarloE

          • Nick C

            March 27, 2013 at 1:39 am

            I have figured out my count issue above – but i do need help on a sumif (or sumifs) query

            =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!E18:E190”),$H10,INDIRECT(“‘”&tabs&”‘!g18:g190”)))

            the above formula has a condition that H10 needs to be in the E18:E190 range.

            Why does my formula BELOW not work to have an additional condition that H11 needs to exist in range B18:B190?

            =SUMPRODUCT(SUMIFS(INDIRECT(“‘”&tabs&”‘!E18:E190”),$H10,INDIRECT(“‘”&tabs&”‘!B18:B190”),$H11,INDIRECT(“‘”&tabs&”‘!g18:g190”)))

          • Carlo Estopia

            March 27, 2013 at 9:29 pm

            Hi Nick,

            Your syntax is wrong.
            SUMIFS and SUMIF are two different things; Although, It shows similar results.

            SUMIFS Syntax: SUMIFS(SUM Range, CriteriaRange1, Criteria1, CriteriaRange2, Criteria2…and so on)

            In your case you’re trying to treat SUMIFS like it’s a SUMIF.

            Cheers.

            CarloE

  37. PT

    January 2, 2013 at 8:11 am

    Dear Mynda
    Thank you very much for the brilliant work. I have two follow up questions. First, how do I write this formula if I want to locate this named range (“tabs”) on a different page from the formula? Ex. Formula is on worksheet named “summary” and the tabs named range is on worksheet named “tabs”. Second, if it is possible to name this formula, something like =Formula(J23), would it reduce the size of my file (now 300Mb)? Because I have to make this calculating the formula over 10000+ rows. I am having a very difficult time trying to figure out the syntax involved in the indirect function. What are the best tools to use to tutor myself? TIA

    Reply
    • Mynda Treacy

      January 2, 2013 at 9:19 pm

      Hi PT,

      You can put the tabs list on any sheet, just make sure when you give it the name that you set the ‘Scope:’ to Workbook.

      You can read a tutorial on INDIRECT here.

      In terms of giving the formula a name to reduce the workbook size, I don’t think it will work. Excel still has to perform the calculation 10000 times. SUMPRODUCT is known to be labor intensive for Excel when crunching through large workbooks.

      I’m not sure of your exact formula but if your workbook is slow make sure your formula doesn’t reference any whole columns. Only reference the cells you need so that Excel isn’t doing any redundant calculations.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
    • PT

      January 26, 2013 at 1:06 pm

      Dear Mynda:
      Hi its me again, PT. If have been very productive with versions of your formula above since my last question, because of your help. I am still finding Indirect intuitively challenging. My question. As a background, I originally had 10 worksheets and a single summary sheet in 1 file. Then I split off the 10 worksheets and put them into their individual workbooks (the single file would no longer open and my OS would freeze). So now I have 10 workbooks and a Summary workbook. Now I would like to take the formula from above:
      =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!J”&ROW(J23)),”>0″,INDIRECT(“‘”&tabs&”‘!J”&ROW(J23))))

      And create a Named Range for column “J” in each of the 10 workbooks. By doing this I figure I can insert new columns between “A” and “J” without having to rename every column that appears after “J” in my Summary workbook (i.e. columns K, L, … DA). Each column including column J is the same for each of the 10 workbooks. I have tried several formulas but I receive #REF! and #NAME! responses. For example I tried renaming every column J in the 10 workbooks, from row 2 to row 15,000, the name “Ticks” and then summing all the column J’s in the Summary file with the following formula:

      =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!”&Ticks&ROW(J23)),”>0″,INDIRECT(“‘”&tabs&”‘!&Ticks&”&ROW(J23))))

      If you could assist me I would very much appreciate it.
      TIA
      PT

      Reply
      • PT

        January 26, 2013 at 1:10 pm

        Clarification: the bottom formula is

        =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!”&[Stock1.xls]Ticks&”!”&ROW(J23)),”>0″,INDIRECT(“‘”&tabs&”‘!&[Stock1.xls]Ticks&”!”&ROW(J23))))

        TIA

        Reply
        • Mynda Treacy

          January 26, 2013 at 3:01 pm

          Hi PT,

          It’s easier if you can send me the file. Can you please log a ticket on the help desk and attach the file.

          Cheers,

          Mynda.

          Reply
  38. PT

    December 18, 2012 at 4:48 am

    Hi Mynda,
    I have tried your formula but it is not working for my challenge, so I am asking you for help.

    I have 10 worksheets in an excel 2003 file, each representing a stock (i.e. 10 stocks), and each row represents one trading day, and each column represents different data or formulas derived from the data during each day. In column “J” of each day of each sheet, I get either positive or negative number. For example cell J23 on each sheet might contain -4, 2, 10, -3, -6, -5, -3, 6, 1, 1 (or whatever the case may be).

    Then I have a summary sheet (worksheet 31) in which I would like to add up all the numbers that are negative, and all the numbers that are positive. In the example my two formulas should give the results -21 and 20. For the negative calculation I tried

    =SUMPRODUCT(SUMIF(INDIRECT(“‘”&Sheet1:Sheet10&”‘!J23:J23”),J23<0,INDIRECT("'"&Sheet1:Sheet10&"'!J23:J23")))

    That's it. How do I do it? Can you help?

    Reply
    • PT

      December 18, 2012 at 5:01 am

      Just a clarification, worksheets 11 through 30 are all unrelated sheets. While my summary sheet is worksheet 31, it could be any sheet.

      Reply
      • Mynda Treacy

        December 18, 2012 at 8:40 pm

        Hi PT,

        You need to list the sheet tabs somewhere in your workbook and give them a named range, e.g. ‘tabs’. You then use that named range in your formula instead of ‘Sheet1:Sheet10’. Like this:

        =SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!J23"),">0",INDIRECT("'"&tabs&"'!J23")))

        You can also download the Excel workbook used in the example above and have a look at what I did.

        Kind regards,

        Mynda.

        Reply
        • PT

          December 22, 2012 at 5:05 am

          Thank you Mynda. The formula works! However, I cannot copy the formula to the next row because the J23 remains J23 and does not change to J24, or J25, and so on. What should I do? TIA

          Reply
          • Mynda Treacy

            December 23, 2012 at 8:27 pm

            Hi PT,

            Here’s the formula for adding up cell J23:

            =SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!J"&ROW(J23)),">0",INDIRECT("'"&tabs&"'!J"&ROW(J23))))

            You can then copy this down the column.

            Kind regards,

            Mynda.

          • Chris

            May 21, 2014 at 1:03 pm

            This post has been very helpful. Thank You. For the formula below, where you add the Row function to make the formula dynamic so that it can be copied. How do you enable it to be copied when it needs to cover a range (as opposed to just one cell as it is below)?

            Thanks again.

            Chris

          • Mynda Treacy

            May 21, 2014 at 1:43 pm

            Hi Chris,

            Glad you found it useful 🙂

            You can simply reference a range in the ROW function like this: ROW(A1:A10)

            If you want the ROW function to always start at A1 then modify it to: ROW($A$1:A10)

            Likewise if you want it to always reference A1:A10 then modify it to: ROW($A$1:$A$10)

            Kind regards,

            Mynda.

  39. KIBRIA

    November 24, 2012 at 6:08 am

    I AM TRYING SUMPRODUCT PROPER FORMULA BUT ANS IS REF# DON’T KNOW WHY IS THERE ANY HELP FOR ME

    Reply
    • Mynda Treacy

      November 24, 2012 at 9:27 am

      Hi Kibria,

      You usually get a #REF! error when one of the formula parameters is pointing to an invalid range.

      Kind regards,

      Mynda.

      Reply
      • Trent

        December 17, 2012 at 3:43 pm

        Or a blank cell in the list of cell names

        Reply
        • Luis Franco

          January 8, 2013 at 10:38 pm

          Bravo!

          Reply
          • Mynda Treacy

            January 8, 2013 at 10:52 pm

            🙂 Cheers, Luis.

      • Joe Lam

        December 29, 2014 at 3:02 pm

        The indirect part should be INDIRECT(“‘”&A1&”‘”&”!E:E”) for the apostrophe. It works

        Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.