simplifying a complex formula and selecting a changing range

sarlo00

Board Regular
Joined
Nov 13, 2002
Messages
105
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
Joined
Nov 13, 2002
Messages
105
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
Joined
Nov 13, 2002
Messages
105
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Nov 13, 2002
Messages
105

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Nov 13, 2002
Messages
105

ADVERTISEMENT

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Nov 13, 2002
Messages
105
Re: simplifying a complex formula and selecting a changing r

Aladin,
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
Joined
Nov 13, 2002
Messages
105
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?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top