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?
Bookmarks