+ Reply to Thread
Results 1 to 4 of 4

SUMIF with unknown criteria

  1. #1
    Registered User
    Join Date
    03-08-2018
    Location
    Derbyshire, England
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    33

    SUMIF with unknown criteria

    Hi,

    I have a sheet with a survey form where I need to sum the quantity of a repair material where I don't know in advance of getting back the surveys from the surveyors, what all the repair materials are. On the sheet there are two tables containing this data which show the material that footpaths and driveways are constructed of, and therefore what they need to be repaired with. In the majority of cases it's going to be either Tarmac or Concrete, and I'm using the formulas
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    to sum the total quantity of each material in a table that summarises the quantities of materials required.

    Where the repair material is neither Tarmac or Concrete though, I want to capture the quantity of the material needed (whatever the material is) in a column called 'other'

    I've attached both the original survey sheet, and for clarity a simplified sheet showing the source table and the summary table. In essence what I'm trying to do is say 'if the repair material is Tarmac or Concrete, sum the quantity of each required, but if the repair material is something else, sum the quantity of that'. In the simplified example I've attached, I'd therefore expect Excel to sum the quantity of 'gravel' and 'block paving' as shown in green on the example. If cells in the ranges C3:C5 and C9:C11 are empty obviously there wouldn't be anything to sum.

    I hope that makes sense, and any assistance appreciated.
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,789

    Re: SUMIF with unknown criteria

    I worked with your simplified version. The formula for Front Paths/Other is

    =SUMIFS($F$3:$F$5,$C$3:$C$5,"<>TARMAC",$C$3:$C$5,"<>CONCRETE")

    for Rear Paths/Other

    =SUMIFS($F$9:$F$11,$C$9:$C$11,"<>TARMAC",$C$9:$C$11,"<>CONCRETE")

    SUMIFS allows you to specify multiple conditions to be met for inclusion in the sum, and all conditions must be met.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    03-08-2018
    Location
    Derbyshire, England
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    33

    Re: SUMIF with unknown criteria

    Thank you so much. That works perfectly! I've also now used this formula with the appropriate cell references to do the same thing with driveways, which has been bugging me for weeks, trying to think of the best way to capture the odd property with a driveway which isn't tarmac or concrete, so that's solved two issues in one go.

    Thanks again for your help!

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,789

    Re: SUMIF with unknown criteria

    You're welcome! Thanks for the rep, and thanks for marking your thread Solved.

+ 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] Sumif unknown criteria
    By S1n1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-28-2016, 02:42 PM
  2. [SOLVED] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 PM
  3. [SOLVED] Max & Min unknown malfunction with multiple criteria
    By smit.etha in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2014, 06:01 PM
  4. Average CountIF or SumIF between unknown ranges?
    By MJSlattery in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2013, 03:40 PM
  5. Replies: 0
    Last Post: 04-18-2012, 06:51 AM
  6. Filter by unknown criteria problem
    By thornton in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2011, 12:56 PM
  7. Display 'UNKNOWN' in a cell if the criteria doesn't match
    By famico78 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2008, 09:55 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