+ Reply to Thread
Results 1 to 4 of 4

How to SumIf over multiple sheets across a range with an If statement

  1. #1
    Registered User
    Join Date
    04-07-2013
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2010
    Posts
    16

    Question How to SumIf over multiple sheets across a range with an If statement

    Hi,

    I am looking for a new and unique way to use a SumIf over multiple sheets. This is an example of what I want it to do, and what I currently have:

    Let's say we have three tabs called Tab1, Tab2, and Tab3. I want the SumIf on a master page to search a column A range for a specific number and return the results from column B in that same range. The If criteria would be if Col B > 50000 Return the value or 0 if < 50000. There will be multiple rows within each worksheet which contain the values we are seeking, so it has to include the range within that worksheet. Here is what I have so far, and all it is doing is pulling the first Tab1: (Braces denote array formula)

    {=SUMIF(INDIRECT("'"&A7:A9&"'!A2:A5"),G2,INDIRECT("'"&A7:A9&"'!B2:B5") )}

    I am unsure where to insert my if command to have it execute through each line of the array and thus have excluded it from the string.

    In this example, I am using an Indirect function to insert the sheet name (i.e. A7 = Tab1, A8= Tab2, A9=Tab3) and the SumIf range in each sheet should be A2:A5 for the reference value and B2:B5 for the Sum value. G2 is the lookup value on the master tab that I want all of the summing to be done on. Any ideas on how I can make these ranges work? My biggest gratitude for anybody that can put me on the right track!

    Thanks!

    Nick

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to SumIf over multiple sheets across a range with an If statement

    Very close!

    Try it like this:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&A7:A9&"'!A2:A5"),G2,INDIRECT("'"&A7:A9&"'!B2:B5")))

    Just normally enter. No need to array enter.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    04-07-2013
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: How to SumIf over multiple sheets across a range with an If statement

    We are on the right track. Now how do I nest an if to ignore all values sum range values smaller than x?
    Last edited by love0126; 12-18-2013 at 10:46 PM.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to SumIf over multiple sheets across a range with an If statement

    Quote Originally Posted by love0126 View Post
    how do I nest an if to ignore all values sum range values smaller than x?
    Instead of using an exclusionary condition use an inclusion condition.

    Instead of ignoring values smaller than x, include those values greater than or equal to x.

    Since you're using Excel 2010:

    =SUMPRODUCT(SUMIFS(INDIRECT("'"&A7:A9&"'!B2:B5"),INDIRECT("'"&A7:A9&"'!B2:B5"),">="&G3,INDIRECT("'"&A7:A9&"'!A2:A5"),G2))

    Where G3 = the number variable

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-23-2013, 06:25 AM
  2. Replies: 0
    Last Post: 08-17-2012, 02:02 PM
  3. how to do sumif over multiple sheets
    By Kwelly in forum Excel General
    Replies: 8
    Last Post: 06-01-2010, 09:16 PM
  4. Nested SUMIF statement or multiple SUMIF's
    By Dan27 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2009, 06:55 AM
  5. Replies: 5
    Last Post: 01-20-2009, 11:56 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1