+ Reply to Thread
Results 1 to 4 of 4

criteria in SUMIFS and exluding value from cells in range

  1. #1
    Registered User
    Join Date
    03-28-2014
    Location
    Slovak Republic
    MS-Off Ver
    Excel 2010
    Posts
    14

    criteria in SUMIFS and exluding value from cells in range

    Hi i need to help with little problem:
    i use formula sumifs , where are any ranges in parts of criteria, but i have one criterium(condition) where i wolud like to search in date cells, form this cells i need exlude year and this compare with number in criteria.

    is it able ?
    Attached Files Attached Files
    Last edited by mmestan; 12-29-2015 at 05:18 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    Re: criteria in SUMIFS and exluding value from cells in range

    You could use this formula in O3:

    =SUMPRODUCT(E2:E8,--(YEAR(A2:A8)=N1))

    which will sum the cells in column E where the year in column A is the same as given in cell N1.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-28-2014
    Location
    Slovak Republic
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: criteria in SUMIFS and exluding value from cells in range

    thanks ... sumproduct is for me very helpfull

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    Re: criteria in SUMIFS and exluding value from cells in range

    You're welcome - thanks for feeding back.

    Pete

+ 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. Sumifs criteria between multiple range in single cells
    By silambarasan.J in forum Excel General
    Replies: 3
    Last Post: 08-18-2015, 05:57 AM
  2. [SOLVED] SUMIFS with one criteria range that contains merged cells
    By r4square in forum Excel General
    Replies: 5
    Last Post: 03-18-2015, 04:33 AM
  3. Excel Query Date Range Exluding Weekends
    By jimbob23 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2014, 09:21 AM
  4. SUMIFS formula to sum a range of cells if certain criteria are met
    By roseme in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2014, 05:41 PM
  5. SUMIFS with a criteria range ?
    By vemix in forum Excel General
    Replies: 6
    Last Post: 12-20-2012, 11:56 PM
  6. Replies: 3
    Last Post: 11-21-2012, 04:57 PM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 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