+ Reply to Thread
Results 1 to 5 of 5

SUMIFS usage

  1. #1
    Registered User
    Join Date
    08-05-2014
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    7

    SUMIFS usage

    Hi All

    I have the following Data ( file attached)

    I am trying to find sum of counts where Region = 'EMEA' and Region = 'NAMER'.

    I am trying to use SUMIFS. It's not working. So where I am going wrong?

    Any other way of doing the same?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: SUMIFS usage

    =sumproduct((a$6:a$10="emea")*(b$6:b$10))

    =sumproduct((a$6:a$10="namer")*(b$6:b$10))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: SUMIFS usage

    Please Login or Register  to view this content.
    is correct.

    Please Login or Register  to view this content.
    Are both searching for conditions where the A6:A10 range has both EMEA and NAMER as their exact entries, which of course is wrong.

    Well, I would just recommend using the D6 formula because it's fast and obvious (easy to read when you come back next month!)

    Bit if you want other options....
    =SUMPRODUCT(B6:B10, --((A6:A10 = "EMEA") + (A6:A10 = "NAMER") > 0))
    Use addition to create an OR conditional in SUMPRODUCT.

    Array constructions...
    {=SUM(IF(A6:A10="EMEA", B6:B10)) + SUM(IF(A6:A10="NAMER", B6:B10))}
    {=SUM(IF(A6:A10="EMEA", B6:B10, IF(A6:A10="NAMER", B6:B10, 0)))}

    confirm with CTRL+SHIFT+ENTER, not just ENTER.

  4. #4
    Registered User
    Join Date
    08-05-2014
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    7

    Re: SUMIFS usage

    Hi Special-K

    Thanks for your quick response. Now if I use your solution I won't get Sum of Counts where both the criteria is matching.

    In this case the answer should be 6. 1+1+2+2.

  5. #5
    Registered User
    Join Date
    08-05-2014
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    7

    Re: SUMIFS usage

    Hi ben_hensel

    Thanks for such a wonderful explanation.

    I really appreciate your efforts.

+ 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. [SOLVED] Looking for something simpler than SUMIFS - SUMIFS
    By YAbdelaal in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-27-2014, 10:38 AM
  2. Workbook usage or Database usage assistance
    By Sunshine601 in forum Excel General
    Replies: 4
    Last Post: 12-12-2013, 08:36 AM
  3. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  4. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  5. Usage Log
    By djfatboyfats in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-05-2007, 08:24 PM

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