+ Reply to Thread
Results 1 to 5 of 5

Conditional sum w/multiple criteria using cell references

  1. #1
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Question Conditional sum w/multiple criteria using cell references

    Hello everybody.
    I'm aware of a conditional formula to make a sum based on multiple criteria. This to include only a manually entered list of text strings. In this case, I'm only interested in Jessica's and Paula's sales figures:

    = ( SUM( SUMIFS(C2:C100; A2:A100; {"Sales"}; B2:B100; {"Jessica";"Paula"} ) )


    The big limitation of this formula is exactly the fact that you can only insert the list of the salespersons directly into the formula. It doesn't allow any cell reference to the list. So in case I have a long list and I want to "flexibilize" it using cell references, this formula won't help.

    Is there any other formula that will?
    Last edited by Luis Ah-Hoy Jr.; 03-14-2017 at 05:45 AM.
    Luis A. D. Ah-Hoy

  2. #2
    Forum Contributor
    Join Date
    06-12-2006
    Location
    Portugal
    MS-Off Ver
    Microsoft 365 Apps for enterprise
    Posts
    212

    Re: Conditional sum w/multiple criteria using cell references

    Hi,

    You can use single SUMIF twice (or more). There you can use cell references...

    Hope this helps,
    Oct

  3. #3
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Re: Conditional sum w/multiple criteria using cell references

    Thanks for trying oteixeira.
    But that's a relatively basic way to do it. I intend on using a more versatile and compact method.

    Reason being:
    1) I like my spreadsheet nice and tidy, with the fewer number of cells and calculations as possible.
    2) I'm looking forward to evolve from that (possible) formula, so I can later on combine "include" and "exclude" criteria. This is another subject, though.

  4. #4
    Forum Contributor
    Join Date
    06-12-2006
    Location
    Portugal
    MS-Off Ver
    Microsoft 365 Apps for enterprise
    Posts
    212

    Re: Conditional sum w/multiple criteria using cell references

    Hi Luis,

    You can use SUMPRODUCT.
    Give the name sales to the area you want to sum and salesp to the are containing the salespersons names. If the salespersons names are on cells C14 and C15 you can user the formula:

    =SUMPRODUCT((sales) * ((salesp=C14)+(salesp=C15)))

    Hope this helps
    OT

  5. #5
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Re: Conditional sum w/multiple criteria using cell references

    Worked beautifully, oteixeira.
    And by doing some tweaks, the formula even allows to combine an "exclude" criteria using cell references.

    Thanks a lot!

+ 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. Countifs - using multiple cell references as part of one criteria
    By davidharper2005 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-01-2015, 02:38 AM
  2. Retrieve multiple cell references based on a criteria
    By Danut Alexandru in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-04-2014, 02:16 PM
  3. Conditional Formatting Multiple Criteria within same cell
    By ianfav in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-17-2013, 09:17 AM
  4. Replies: 3
    Last Post: 04-16-2013, 11:57 AM
  5. Replies: 3
    Last Post: 11-21-2012, 04:57 PM
  6. Multiple Criteria and Date references
    By themax16 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-20-2007, 12:46 PM
  7. Return multiple cell references based on a search criteria
    By ruddocg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-30-2006, 07:49 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