+ Reply to Thread
Results 1 to 7 of 7

Sumproduct with multiple critera, and summing multiple Columns

  1. #1
    Registered User
    Join Date
    05-31-2012
    Location
    lemoore, CA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Sumproduct with multiple critera, and summing multiple Columns

    I would like to create 2 formulas.

    I have Los Angeles county which is divided into LA Region1 and LA region 2. This is denoted in the "region" column. And I have 4 other columns where Los Angeles could be listed. I only want to sum (not count) the values of the 4 Los Angeles Columns IF the region says LA Region 1 or LA region 2. (2 separate formulas)

    My formula is now:

    LA region 1 =SUMPRODUCT((Master!$AK$2:$AK$56="Los Angeles")*(Master!$C$2:$C$56="LA Region 1")*(Master!$AL$2:$AL$56)+(Master!$C$2:$C$56="LA Region 2")*(Master!AM2:AM56="Los Angeles")*(Master!AN2:AN56)+(Master!AO2:AO56="Los Angeles")*(Master!AP2:AP56)+(Master!AQ2:AQ56="Los Angeles")*(Master!AR2:AR56))

    LA Region 2=SUMPRODUCT((Master!$AK$2:$AK$56="Los Angeles")*(Master!$C$2:$C$56="LA Region 2")*(Master!$AL$2:$AL$56)+(Master!$C$2:$C$56="LA Region 2")*(Master!AM2:AM56="Los Angeles")*(Master!AN2:AN56)+(Master!AO2:AO56="Los Angeles")*(Master!AP2:AP56)+(Master!AQ2:AQ56="Los Angeles")*(Master!AR2:AR56))
    The problem is when I add a value in the range (for instance)AN2:AN56 it adds to both formulas which is not right.

    I would like my 2 formulas to read Sum (Value column 1 and Value column 2 and Value column 3 and Value column 4) IF REGION ="LA Region 1" AND "Los Angeles" appears in ANY 1 OF THESE COLUMNS(Additional County Name 1 Additional County Name 2 Additional County Name 3 Additional County Name 4)

    The same thing for LA Region 2.

    Can someone help me please?

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Sumproduct with multiple critera, and summing multiple Columns

    Try this,

    =SUMPRODUCT((Master!$AK$2:$AQ$56="Los Angles")*MOD(COLUMN($AK$2:$AQ$2)-COLUMN($AK$2)+1,2)*(Master!$C$2:$C$56="Range 1"),Master!$AL$2:$AR$56)

    If you want to sum Range 1 & 2,

    =SUMPRODUCT((Master!$AK$2:$AQ$56="Los Angles")*MOD(COLUMN($AK$2:$AQ$2)-COLUMN($AK$2)+1,2)*ISNUMBER(MATCH(Master!$C$2:$C$56,{"Range 1","Range 2"},0)),Master!$AL$2:$AR$56)
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    05-31-2012
    Location
    lemoore, CA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Sumproduct with multiple critera, and summing multiple Columns

    No I am getting 0s back.

  4. #4
    Registered User
    Join Date
    05-31-2012
    Location
    lemoore, CA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Sumproduct with multiple critera, and summing multiple Columns

    My values that I want to sum are in a differ are in the column next to The word Los Angeles.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Sumproduct with multiple critera, and summing multiple Columns

    Does this work?

    =SUMPRODUCT((Master!$AK$2:$AQ$56="Los Angeles")*(Master!$C$2:$C$56="LA Region 1"),Master!$AL$2:$AR$56)
    Audere est facere

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Sumproduct with multiple critera, and summing multiple Columns

    May be that because of typo in the formula "Los Angles". "e" was missing Los Angeles

  7. #7
    Registered User
    Join Date
    05-31-2012
    Location
    lemoore, CA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Sumproduct with multiple critera, and summing multiple Columns

    Yes It Works! After all of that. thank you daddylonglegs

+ 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