+ Reply to Thread
Results 1 to 3 of 3

sumifs, vlookup, multiple column rules

  1. #1
    Registered User
    Join Date
    11-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Mac 2011
    Posts
    34

    sumifs, vlookup, multiple column rules

    Hi,

    In my spreadsheet I have 1 column with a range of different values, one with either "a" "b" or "c", one with "yes" or blank, and one with number 1-7.

    Column 1 are the values i want to sum together but on some conditions...
    - only include the value in the sum if the 3rd column is blank (omit if reads "yes")
    - only sum together values which have the same number/letter in 2nd and 4th column (ie all A1's, all B5's etc)

    I want to write a formula to sum these so that when the sort of the table changes the calculation doesn't change.

    Workbook2.xlsx
    Last edited by will.girling; 03-11-2014 at 11:00 AM. Reason: solved

  2. #2
    Registered User
    Join Date
    07-03-2013
    Location
    Montreal, QC
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: sumifs, vlookup, multiple column rules

    This formula should work, but you will have to use one formula for each possible combinations of the Type & Number columns.

    Type/Number Formula
    A0 =SUMIFS(A:A,B:B,"A",C:C,"<>YES",D:D,0)
    A1 =SUMIFS(A:A,B:B,"A",C:C,"<>YES",D:D,1)
    A2 =SUMIFS(A:A,B:B,"A",C:C,"<>YES",D:D,2)
    A3 =SUMIFS(A:A,B:B,"A",C:C,"<>YES",D:D,3)
    ...
    B0 =SUMIFS(A:A,B:B,"B",C:C,"<>YES",D:D,0)
    B1 =SUMIFS(A:A,B:B,"B",C:C,"<>YES",D:D,1)
    B2 =SUMIFS(A:A,B:B,"B",C:C,"<>YES",D:D,2)
    ...

    Regards

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: sumifs, vlookup, multiple column rules

    This works as I understand your problem. It uses two helper columns that can be hidden.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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 Multiple Criteria Same Column
    By jsharrard in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-18-2017, 05:08 PM
  2. sumifs with multiple criteria in one column.
    By apolloni in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-10-2014, 05:13 AM
  3. [SOLVED] SumIfs with multiple criteria (search column for cells w/ text containing)
    By jerrydiaz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-22-2013, 01:06 PM
  4. 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
  5. Sum Mulitple Column based on the multiple Conditions using sumifs function
    By JEETKAMALARORA in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-20-2012, 03:24 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