+ Reply to Thread
Results 1 to 4 of 4

SUMIF Formula Help

  1. #1
    Registered User
    Join Date
    05-07-2018
    Location
    United Kingdom
    MS-Off Ver
    Mac Office 2019
    Posts
    2

    Exclamation SUMIF Formula Help

    Hi, I am trying to set up a formula which will sum the numbers in one column that meet the following criteria in two other columns: a particular month (in one adjacent column) and greater than or equal to +0.5 (in a second adjacent column). I've put the following formula in but can't get it to work. =SUMIFS(D2:D697,(SUMPRODUCT(--(MONTH($A$2:$A$697)=1),--($M$2:$M$697>=0.5))))

    The range I want to sum based on the adjacent columns is D2:D697, with column A (date column) being January (=1) and also based on column M being greater than or equal to +0.5.



    I'm also using the following which works elsewhere in the worksheet perfectly so I thought it would be a simple case of adding the SUMIF at the beginning.... =SUMPRODUCT(--(MONTH($A$2:$A$697)=1),--($M$2:$M$697>=0.5))

    Thanks.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,052

    Re: SUMIF Formula Help

    Try...

    =SUMPRODUCT(--(MONTH($A$2:$A$697)=1),--($M$2:$M$697>=0.5),$D$2:$D$697)
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    05-07-2018
    Location
    United Kingdom
    MS-Off Ver
    Mac Office 2019
    Posts
    2

    Re: SUMIF Formula Help

    Hi Jeff,

    Works fantastically, thanks! One more question, if I wanted to do the average rather than the sum for the above range what would the formula change to?

    Thanks.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,052

    Re: SUMIF Formula Help

    How about...

    =SUMPRODUCT(--(MONTH($A$2:$A$697)=1),--($M$2:$M$697>=0.5),$D$2:$D$697)/SUMPRODUCT(--(MONTH($A$2:$A$697)=1),--($M$2:$M$697>=0.5))

+ 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] My formula sumif/indirect formula is working perfect..except everything is 1 row off!
    By Nyolls in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2015, 01:12 PM
  2. SumIf formula or Sumif with pivottable combined question
    By mcgleeuw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2015, 01:05 PM
  3. Replies: 2
    Last Post: 09-16-2014, 10:13 AM
  4. [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
  5. SUMIF or SUMIF's formula with multiple criteria
    By jackiemariee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 06:19 PM
  6. [SOLVED] Sumif Formula or Sumproduct Formula? Three Criteria.
    By oHUTCHYo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2013, 10:50 AM
  7. SumIF formula with criteria is Text inside a Formula?
    By excellentlearner in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-25-2012, 06:40 PM

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