+ Reply to Thread
Results 1 to 6 of 6

Sum if range criteria is met then evaluate results and return specified value

  1. 09-23-2015, 12:54 PM #1
    jbeets
    jbeets is offline
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

    Sum if range criteria is met then evaluate results and return specified value

    Hello,

    I need to sum a range if the day of week in the range equals Saturday and then once that sum is determined I need the formula to review the total and determine if it is greater than “6”. If it is than enter “6” otherwise enter the original summed value. I have gotten this far with this array formula however I am not coming up with the correct value.

    This formula currently returns whatever value meets both criteria or it enters 0. Instead of returning 6 it returns 10. If the sum value is 3 then it should return 3.

    =SUM(IF((B3:H3=A9)*(B4:H4>B10),B4:H4))

    Thank you in advance for any help you can provide!

    Jbeets
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-23-2015, 01:40 PM #2
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Sum if range criteria is met then evaluate results and return specified value

    How about:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    or

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Which is the same formula but without the hard coding of values.

    BSB
    Reply With QuoteRegister To Reply
  3. 09-23-2015, 01:56 PM #3
    MushroomFace
    MushroomFace is offline
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    78

    Re: Sum if range criteria is met then evaluate results and return specified value

    Hi

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    The formula is saying;
    "IF(INDEX($B$4:$H$4,MATCH($A$9,$B$3:$H$3,0))<10,"
    If, the Saturdays value is smaller than 10,
    The index is just like a vlookup/hlookup

    INDEX($B$4:$H$4,MATCH($A$9,$B$3:$H$3,0)),"6")
    bring back the lookup value (same formula as above without the ending <10, otherwise return 6

    Although this is a look up, as there seems to only be one Saturday at any one time? If there were multiple it would have to be a sumif.

    Last edited by MushroomFace; 09-23-2015 at 01:59 PM.
    Reply With QuoteRegister To Reply
  4. 09-23-2015, 02:07 PM #4
    jbeets
    jbeets is offline
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

    Re: Sum if range criteria is met then evaluate results and return specified value

    WOW! You guys are the best! Thank you so much! This works perfectly!!! I tested both options and they work perfectly!
    Reply With QuoteRegister To Reply
  5. 09-23-2015, 02:13 PM #5
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Sum if range criteria is met then evaluate results and return specified value

    Only easy when you know how!

    BSB
    Reply With QuoteRegister To Reply
  6. 09-23-2015, 02:24 PM #6
    MushroomFace
    MushroomFace is offline
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    78

    Re: Sum if range criteria is met then evaluate results and return specified value

    Sweet Keep them coming
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA return formula results as values in Range
    By thegun616 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-19-2015, 10:02 AM
  2. [SOLVED] VLOOKUP between workbooks, don't return results matching a 2nd column criteria?
    By Tunesmith in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2014, 11:58 AM
  3. [SOLVED] Return multiple results when a criteria is met
    By kpodoh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2014, 11:49 AM
  4. Search Workbook for cells that meet multiple criteria and return results from rows
    By CFritz7 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-27-2013, 04:41 PM
  5. Return Results Based on Date Range
    By ExcelSuduko in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-05-2013, 11:23 AM
  6. Vlookup and return value from a range of results?
    By homer123456 in forum Excel General
    Replies: 10
    Last Post: 01-11-2011, 07:41 PM
  7. Return results based on multiple criteria
    By Bryce in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-28-2005, 08:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1