+ Reply to Thread
Results 1 to 8 of 8

Formula to return value based on multiple criteria including specific date range

  1. #1
    Registered User
    Join Date
    04-07-2022
    Location
    UK
    MS-Off Ver
    MS 365 MSO Windows 11 64-bit
    Posts
    4

    Formula to return value based on multiple criteria including specific date range

    Hello,

    I need to find a formula that will return a sum value () - it must look in Column A for a specific code that is on another worksheet e.g. IT300-100, sum any costs against that code (may be multiple lines of costs) between a specific date range e.g. Jan 2022 <31/01/2021 to >01/02/2022.

    My first part of the code would usually be =SUMIFS('Forecast Spend'!,A6:A1000,A19,'Forecast Spend!',N6:N1000)

    The date range would be another formula = =SUMIFS('Forecast Spend'!N6:N1035,'Forecast Spend'!C6:C1035,">31/12/2021",'Forecast Spend'!C6:C1035,"<01/02/2022")

    But how can I combine the two?

    Many thanks

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,648

    Re: Formula to return value based on multiple criteria including specific date range

    Administrative Note:

    Welcome to the forum.

    Members will tailor the solutions they offer to the version (NOT a release number like 2203) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    04-07-2022
    Location
    UK
    MS-Off Ver
    MS 365 MSO Windows 11 64-bit
    Posts
    4

    Re: Formula to return value based on multiple criteria including specific date range

    Hi Ali,

    Thank you for you help.

    I have updated my Version - hope this is OK now

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,648

    Re: Formula to return value based on multiple criteria including specific date range

    Thanks.

    Can you explain why the ranges in the two formulae are different? If you want to combine them into one formula (one SUMIFS?), you'll need to make them the same dimension.

    It would help if you provided a sample workbook.

  5. #5
    Registered User
    Join Date
    04-07-2022
    Location
    UK
    MS-Off Ver
    MS 365 MSO Windows 11 64-bit
    Posts
    4

    Re: Formula to return value based on multiple criteria including specific date range

    Hi Ali,

    Yes I need to combine the two formulas. So it looks for a specific code on one tab and also in another column in a different tab (same code may be used multiple times in one column). Then bring back any costs against that code (different column) within a certain time range e.g. January (another column). I have attached a sample spreadsheet, so I'd need Tab1 populated by month - based upon criteria in Tab2. It should return 200 for Jan22 on Tab1. Thanks
    Attached Files Attached Files
    Last edited by ShellBeatz; 04-07-2022 at 11:46 AM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,084

    Re: Formula to return value based on multiple criteria including specific date range

    Try pasting the following into cell C4 on the Tab1 sheet:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Once the formula is activated, then drag and fill handle over and down.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    04-07-2022
    Location
    UK
    MS-Off Ver
    MS 365 MSO Windows 11 64-bit
    Posts
    4

    Re: Formula to return value based on multiple criteria including specific date range

    Hi JeteMc,

    Thank you so much for your help, it works! You're a superstar!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,084

    Re: Formula to return value based on multiple criteria including specific date range

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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] yes-no based on multiple criteria including contains specific text
    By Nina07 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-31-2022, 05:09 AM
  2. Return Max with multiple criteria including date - array?
    By helldizzle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-13-2016, 09:10 PM
  3. [SOLVED] Count and sum with multiple criteria including date range
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-23-2016, 04:46 PM
  4. Formula to return values based on criteria and date range
    By jr217 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2014, 06:20 AM
  5. [SOLVED] INDEX MATCH based on 5 criteria, including a date range.
    By Folshot in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2013, 09:35 PM
  6. [SOLVED] Count based on 3 criteria including date range
    By timarcarze in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 06-13-2013, 01:43 AM
  7. Replies: 5
    Last Post: 05-23-2011, 12:04 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1