+ Reply to Thread
Results 1 to 13 of 13

Sumif with an average

  1. #1
    Registered User
    Join Date
    10-08-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    43

    Sumif with an average

    Hi all,

    I'm trying to compare an average of three cells to 0 and if that average is greater than 0 i want to sum another column. So I can do this for one cell rather than an average of three cells with this formula:

    =SUMIF(N9:N110,">0",$I9:$I110)

    So for the rows 9 to 110 if N is positive I include the corresponding value in column I. That's straightforward enough. The trouble I'm having is when I try to switch the N9:N110 to average(L9:N110) in order to check the average of L9,M9 and N9 and compare that to 0.

    Any help would be appreciated.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Sumif with an average

    Just use an IF statement

    =IF(AVERAGE($N9:$N110)>0, SUM($I9:$I110), "")

    Not sure where L9 and M9 come into this equation???
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    10-08-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    43

    Re: Sumif with an average

    So Its not the average of n9 to n110 that i want, its the average of the rows, so is l9, m9 and n9 average greater than zero iu want to use i9 in my sum.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Sumif with an average

    Maybe a worksheet showing what you want would help (Go Advanced>Manage Attachments)

    I think
    =IF(AVERAGE($L9, $N9, $M9)>0, SUM($I9:$I110), "")

  5. #5
    Registered User
    Join Date
    10-08-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    43

    Re: Sumif with an average

    So that suggestion says if the average of line 9 is greater than 0 the sum all of column I. What I'm looking for is slightly different. Its if the avergae of row 9 is greater than 0 then include i9 in the sum, if it isn't then don't include it in the sum, and repeat for each row.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Sumif with an average

    Ahhhh, is there always 3 numbers? (i.e. the cells in L,N, M will have values?)

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Sumif with an average

    Try this ARRAYED Formula

    =SUM(IF((L9:L110+M9:M110+N9:N110)/3 >0, I9:I110))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  8. #8
    Registered User
    Join Date
    10-08-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    43

    Re: Sumif with an average

    this was helpful and worked. It seems like every time I don't know how to do something an array is the solution.I just never know how to use them

    Thanks

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Sumif with an average

    Glad it worked. There might be a non-array solution out there but it didn't pop into my head.
    Thanks for the rep.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumif with an average

    Quote Originally Posted by ChemistB View Post
    There might be a non-array solution out there but it didn't pop into my head.
    Maybe this...

    =SUMPRODUCT(--(L9:L110+M9:M110+N9:N110/3>0),I9:I110)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Sumif with an average

    That would be the one.

  12. #12
    Registered User
    Join Date
    10-08-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    43

    Re: Sumif with an average

    What does the double dash do?

  13. #13
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Sumif with an average

    Changes TRUE/FALSE Values to 1's and 0's.

+ 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] Ignore #N/A in sumif and average if
    By justinr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-14-2015, 04:08 PM
  2. [SOLVED] SUMIF with weighted average
    By pansovic in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-03-2012, 06:29 PM
  3. Average using Sumif
    By Devi S in forum Excel General
    Replies: 5
    Last Post: 01-19-2012, 11:25 AM
  4. Find Average of SUMIF
    By Jogier505 in forum Excel General
    Replies: 4
    Last Post: 10-13-2010, 01:24 PM
  5. SUMIF Date / Average Help
    By bomberchia in forum Excel General
    Replies: 9
    Last Post: 11-26-2008, 08:43 AM
  6. Sumif, indirect and average?
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-04-2007, 08:41 AM
  7. Average using SUMIF/COUNTIF help...
    By scoobz in forum Excel General
    Replies: 1
    Last Post: 02-08-2005, 10:15 AM

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