+ Reply to Thread
Results 1 to 2 of 2

Sumif contains text from list

  1. #1
    Registered User
    Join Date
    10-11-2014
    Location
    Noitacol, Acirema
    MS-Off Ver
    Office 365 2019
    Posts
    53

    Question Sumif contains text from list

    So, if you look at the example sheet I've attached, you'll see my issue in the "amount" column on the Totals Table.
    I'm using this formula =ABS(SUMPRODUCT(SUMIF(Table2[Description],Loan,Table2[Amount]))) to sum the total amount based on criteria from a list (List Tables section)
    What I want to do is instead of typing in the list on each line individually, I want to be able to just reference the text in the description column, but have it use the actual list of the same name, for example I want to be able to use this formula and get the same result =ABS(SUMPRODUCT(SUMIF(Table2[Description],[@Description],Table2[Amount]))) So the description column on the Totals Table (example Loan) will not just be the text "Loan" it will be the actual list labeled "Loan"
    IS this possible? Did I explain myself correctly (kind of a tough 1 to explain)
    I also placed a screenshot of the name manager just for better clarification
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Sumif contains text from list

    You can use INDIRECT, e.g. INDIRECT([@Description]) , which will cast the literal string into a Range reference

    however, be warned, the SUMPRODUCT becomes Volatile as a result, so keep an eye on performance

    above assumes that the source ranges will be located in the same workbook as the INDIRECT calc, if not the file containing the source range(s) will need to be open at the point of calculation.
    Last edited by XLent; 12-17-2019 at 02:09 PM. Reason: added usual caveat re: INDIRECT

+ 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: 11-01-2017, 02:01 PM
  2. SUMIF and list
    By sklinderman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-24-2014, 07:21 AM
  3. [SOLVED] Search for the text in cell A1 in a list and remove the text if found in the list
    By kak0 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-24-2014, 11:01 PM
  4. [SOLVED] How to SUMIF name is one from a list.
    By beesus311 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-17-2013, 06:26 AM
  5. Replies: 12
    Last Post: 07-30-2013, 11:52 AM
  6. Vlookup text in 1 list to a similar text in 2nd list.. possible??
    By kev_33 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 04-23-2013, 05:29 AM
  7. [SOLVED] List SUMIF
    By Kip in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-09-2005, 08:05 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