+ 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
    9,603

    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
    9,603

    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)

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