# Sumproduct with multiple critera, and summing multiple Columns

1. ## 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.

2. ## 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)

3. ## Re: Sumproduct with multiple critera, and summing multiple Columns

No I am getting 0s back.

4. ## 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. ## 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)

6. ## 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. ## Re: Sumproduct with multiple critera, and summing multiple Columns

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

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

#### 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