# simplifying a complex formula and selecting a changing range

#### sarlo00

##### Board Regular
I have a sheet (call this sheet Sheet2!) that I've had help on in several other posts which looks like this:
Book2
ABCDEF
1THEABCCOMPANYBCA-ITSCustomPPO-0
2BCCITSCustomPPOITSCustom15
3BCD---0
4BCE---0
5BCF---0
6BCG---0
7BCHITSCustom-20
8BCJ---0
9BCKITSCustom--3
10BCNITSCustomPPO--914
11BCO---0
12BCPITSCustomPPO--8
13BCQITSCustomPPO--16
14BCT---0
15BCV---0
16BCWITSCustomPPO--30
17BCY---0
18BCZITSCustomPPO--532
Sheet2

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### sarlo00

##### Board Regular
Re: simplifying a complex formula and selecting a changing r

I now need to create a sheet that summarizes this information. One formula in particular is giving me problems:

=IF(""=(\$A2),SUMIF(Sheet2!\$C\$1:\$C\$18,INDEX(\$B1:\$B\$94,MATCH(TRUE,(\$A2:\$A\$94<>""),0)-MATCH(TRUE,(\$A2:\$A\$94<>""),0)+1,0),Sheet2!\$F\$1:\$F\$18),IF(AND(\$A1=Sheet2!\$A1,Sheet1!\$B1=Sheet2!\$C1),Sheet2!\$F1,"Error"))

I want to know if there is a way to simplify this formula. It is in column C of this sheet which is Sheet1!.
Book2
ABCD
1THEABCCOMPANYITSCustomPPO1505
2ITSCustom3
3-0
4-0
5-0
6-0
7-0
8-0
9-0
10-0
11-0
12-0
13-0
14-0
15-0
16-0
17-0
18-
19TheDCECompany
Sheet1

#### sarlo00

##### Board Regular
Re: simplifying a complex formula and selecting a changing r

Most Companies in Col A have only one value in Col B. The formula says, if there is a blank cell below the company name, sum the total of all the different values associated with Col B. If not, return the corresponding cell in Sheet2!.

My main problem is that the number of choices in Sheet2! column B will change monthly. I need a way to select the SUMIF range without selecting it manually.

=IF(""=(\$A2),SUMIF(Sheet2!\$C\$1:\$C\$18,INDEX(\$B1:\$B\$94,MATCH(TRUE,(\$A2:\$A\$94<>""),0)-MATCH(TRUE,(\$A2:\$A\$94<>""),0)+1,0),Sheet2!\$F\$1:\$F\$18),IF(AND(\$A1=Sheet2!\$A1,Sheet1!\$B1=Sheet2!\$C1),Sheet2!\$F1,"Error"))

I cannot figure out a way to select this range without directly referencing it.

##### MrExcel MVP
Re: simplifying a complex formula and selecting a changing r

Does the column B in Sheet2 always list 18 items per company?

#### sarlo00

##### Board Regular

Re: simplifying a complex formula and selecting a changing r

No. Some companies have only 2. Also, The ACB Company may have 20 next month or it may have 15. That is why I added the MATCH(TRUE, portion of the formula, which counts the number of rows before the next company name.

##### MrExcel MVP
Re: simplifying a complex formula and selecting a changing r

sarlo00 said:
No. Some companies have only 2. Also, The ACB Company may have 20 next month or it may have 15. That is why I added the MATCH(TRUE, portion of the formula, which counts the number of rows before the next company name.

Why not repeat the company name in Sheet2? Such a change in your set up would allow you to use efficient formulas for summarizing?

#### sarlo00

##### Board Regular

Re: simplifying a complex formula and selecting a changing r

If I did repeat the company names in Sheet2, is there then a way to select the range of rows for that company? Could I then use
Sheet2!\$A\$1=Sheet1!\$A\$1:\$A\$200 in somw way?

##### MrExcel MVP
Re: simplifying a complex formula and selecting a changing r

sarlo00 said:
If I did repeat the company names in Sheet2, is there then a way to select the range of rows for that company? Could I then use
Sheet2!\$A\$1=Sheet1!\$A\$1:\$A\$200 in somw way?
sarlo00.xls
ABCDEF
1CompanyMethodTotalStartEnd
2THEABCCOMPANYITSCustomPPO1505118
3ITSCustom3118
4-0118
5-0118
6-0118
7-0118
8-0118
9-0118
10-0118
11-0118
12-0118
13-0118
14-0118
15-0118
16-0118
17-0118
18-0118
19-0118
20TheDCECompanyITSCustom#N/A#N/A#N/A
21
Sheet1

Formulas...

C2:

=IF(B2<>"-",SUMIF(INDEX(Sheet2!C:C,D2):INDEX(Sheet2!C:C,E2),B2,INDEX(Sheet2!F:F,D2):INDEX(Sheet2!F:F,E2)),0)

D2:

=MATCH(IF(A2<>"",A2,LOOKUP(REPT("z",255),\$A\$1:A1)),Sheet2!A:A,0)

E2:

=IF(ISNA(D2),#N/A,MATCH(IF(A2<>"",A2,LOOKUP(REPT("z",255),\$A\$1:A1)),Sheet2!A:A))

#### sarlo00

##### Board Regular
Re: simplifying a complex formula and selecting a changing r

Would you please re-post your last table. There is an error on the page that won't allow me to view each formula on the sheet. Also,
why would you lookup a cell with 255 Zs?

#### sarlo00

##### Board Regular
Re: simplifying a complex formula and selecting a changing r

Would someone help me understanding the use of REPT("z",255)?

LOOKUP(REPT("z",255),A1:A20) returns the contents of the lowest row with text data in it?

MATCH(REPT("z",255),A1:A20) returns the row number of the largest text string in Column A?

Or does it return the row number of the last text string in Column A?

Replies
11
Views
241
Replies
3
Views
133
Replies
3
Views
81
Replies
1
Views
40
Replies
0
Views
93

1,130,047
Messages
5,639,767
Members
417,110
Latest member
RayClarke

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back