+ Reply to Thread
Results 1 to 3 of 3

SUMIF formula - adding another variable..

Hybrid View

  1. #1
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    SUMIF formula - adding another variable..

    I have the following formula courtesy of Daddylonglegs but I need to add another 2 variables to it.

    =SUM(SUMIF(Properties!$S$3:$S$1002,{"Y","A"},Properties!$CH$3:$CH$1002))/MAX(1,SUM(COUNTIFS(Properties!$S$3:$S$1002,{"Y","A"},Properties!$CH$3:$CH$1002,"<>")))
    I need the formula to check if column s = Y or A and column v = Y or A then, sum column CH

    This also needs to be applied to the countifs statement.

    I have tried the following but I just get and error message, getting really stuck now.

    =SUM(SUMIF((Properties!$S$3:$S$1002,{"Y","A"}*Properties!$v$3:$v$1002,{"Y","A"}),Properties!$CH$3:$CH$1002))/MAX(1,SUM(COUNTIFS(Properties!$S$3:$S$1002,{"Y","A"}*Properties!$v$3:$v$1002,{"Y","A"}),Properties!$CH$3:$CH$1002,"<>")))
    Last edited by AllenMead; 09-30-2010 at 02:18 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: SUMIF formula - adding another variable..

    In terms of modifying the SUMIF(S) approach:

    =SUM(SUMIFS(Properties!$CH$3:$CH$1002,Properties!$S$3:$S$1002,{"Y","Y","A","A"},Properties!$V$3:$V$1002,{"Y","A","Y","A"}))/MAX(1,SUM(COUNTIFS(Properties!$S$3:$S$1002,{"Y","Y","A","A"},Properties!$V$3:$V$1002,{"Y","A","Y","A"},Properties!$CH$3:$CH$1002,"<>")))
    else, in Array terms

    =IFERROR(AVERAGE(IF(ISNUMBER(MATCH(Properties!$S$3:$S$1002&"^"&Properties!$V$3:$V$1002,{"A^Y","A^A","Y^A","Y^Y"},0))*(Properties!$CH$3:$CH$1002<>""),Properties!$CH$3:$CH$1002)),0)
    confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
    Last edited by DonkeyOte; 09-30-2010 at 03:56 AM. Reason: added IFERROR to ape the SUMIFS

  3. #3
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: SUMIF formula - adding another variable..

    Spot on with both! Thanks for the help, much appreciated! (apologies for the late reply, I had to leave and come back)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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