+ Reply to Thread
Results 1 to 5 of 5

Sumifs with or statement over multiple columns

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    United States
    MS-Off Ver
    2010
    Posts
    41

    Sumifs with or statement over multiple columns

    I'm working on a problem where I'd like to sum the data on column A. I'd like it to sum the value if column B equals "b" OR if the value in column C equals "a". I've attached the example.

    I've tried a bunch of ways but I can't figure it out. It's driving me nuts.

    Workbook1.xlsx

    Thanks!

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Sumifs with or statement over multiple columns

    Maybe this

    =SUM(SUMIF(B1:B11,"B",A1:A11),SUMIF(C1:C11,"A",A1:A11))
    Last edited by AlKey; 10-07-2015 at 10:29 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    09-27-2015
    Location
    Rhode Island, USA
    MS-Off Ver
    2013
    Posts
    35

    Re: Sumifs with or statement over multiple columns

    Do a Sum and Sumif.

    And in case that there's a situation Col B = b AND Col C = A there's a logical test... to check if it's true, and if it's true then we take it out of the total.

  4. #4
    Registered User
    Join Date
    08-21-2013
    Location
    United States
    MS-Off Ver
    2010
    Posts
    41

    Re: Sumifs with or statement over multiple columns

    Is there a way to do this without adding a column?

  5. #5
    Registered User
    Join Date
    09-27-2015
    Location
    Rhode Island, USA
    MS-Off Ver
    2013
    Posts
    35

    Re: Sumifs with or statement over multiple columns

    You mean get rid of the true false column... yeah you can do it this way:

    Same formula only the last part is a sumproduct calculation.

    Your total will now be exactly how you want it to be... in case we have a situation of both col B has a B value and col C has a A value that amount is excluded from the total.

    =SUM(SUMIF(B1:B11,"B",A1:A11),SUMIF(C1:C11,"A",A1:A11))-SUMPRODUCT((B1:B11="b")*(C1:C11="a"),A1:A11)

    Hilmy

+ 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] SUMIFS/AVERAGEIFS across multiple columns
    By keith740 in forum Excel General
    Replies: 30
    Last Post: 06-25-2015, 01:25 PM
  2. SUMIFs across multiple columns (SUMPRODUCT?)
    By source in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 01-26-2015, 02:39 PM
  3. [SOLVED] SUMIFS statement needed to pull multiple data from the same column
    By onetoncrewcab in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-17-2014, 08:27 PM
  4. Can I use sumifs on a range with multiple columns?
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2014, 03:11 AM
  5. Multiple Criteria Sumifs in Multiple Columns
    By arowberry in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-04-2014, 06:06 PM
  6. SUMIFS with multiple sum range columns
    By bvmk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-11-2013, 03:39 PM
  7. [SOLVED] sumproduct and sumifs to create or statement for two columns help
    By draconius in forum Excel General
    Replies: 1
    Last Post: 09-22-2012, 04:18 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