+ Reply to Thread
Results 1 to 3 of 3

Sumifs Formula Using Table as Criteria

Hybrid View

  1. #1
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Sumifs Formula Using Table as Criteria

    Good evening,

    I am wondering if it is possible to use a Table as criteria in a SUMIFS formula. I used {} to include a smaller list of critiera; however that list has grown to over 400+ items. Please see below for curent formula:

    =SUMIFS('Raw Data'!$BT$2:$BT$22735,'Raw Data'!$CI$2:$CI$22735,"At IDC",'Raw Data'!$B$2:$B$22735,{"24766471","25126081","23754675","25207479","25585706","25125999","25585730","25294210","22376936","25770100","25294021","24934160","25294357","25738112","24589104","25585241","25439918"})
    Is it possible to to replace the bracketed section above with Table1 or something of that nature instead of typing in all 400+ values.

  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: Sumifs Formula Using Table as Criteria

    Put the criteria in a range of cells then use this syntax:

    =SUMPRODUCT(SUMIFS('Raw Data'!$BT$2:$BT$22735,'Raw Data'!$CI$2:$CI$22735,"At IDC",'Raw Data'!$B$2:$B$22735,range))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,652

    Re: Sumifs Formula Using Table as Criteria

    Use SUMPRODUCT

    Assuming Criteria_Table in W1:W100

    =SUMPRODUCT(('Raw Data'!$CI$2:$CI$22735="At IDC")*COUNTIF(W1:W100,'Raw Data'!$B$2:$B$22735)*'Raw Data'!$BT$2:$BT$22735)
    Attached Files Attached Files
    Quang PT

+ 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. I want Sumifs formula with Or criteria in same Range
    By lulupatel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-19-2014, 12:03 PM
  2. [SOLVED] formula as criteria in SUMIFS
    By strud in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-03-2014, 08:05 AM
  3. Replies: 5
    Last Post: 06-06-2013, 05:12 PM
  4. [SOLVED] sumifs formula one range with two criteria
    By avk in forum Excel General
    Replies: 7
    Last Post: 07-13-2012, 02:13 PM
  5. Replies: 1
    Last Post: 05-16-2011, 05:00 PM

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