Longest Formula Ever

**astole** · 08-01-2013, 04:57 AM

Just for giggles

!

=IF(E7=M78,SUM(I7:J12)/12,IF(E7=M79,SUM(I7:J12)/6,IF(E7=M80,SUM(I7:J12)/4,IF(E7=M81,SUM(I7:J12)/2,IF(E7=M82,SUM(I7:J12),IF(E7=M83,SUM(I7:J12)*2,IF(E7=M84,SUM(I7:J12)*26/12,IF(E7=M85,SUM(I7:J12)*4,IF(E8=M78,SUM(I7:J12)/12,IF(E8=M79,SUM(I7:J12)/6,IF(E8=M80,SUM(I7:J12)/4,IF(E8=M81,SUM(I7:J12)/2,IF(E8=M82,SUM(I7:J12),IF(E8=M83,SUM(I7:J12)*2,IF(E8=M84,SUM(I7:J12)*26/12,IF(E8=M85,SUM(I7:J12)*4,IF(E9=M78,SUM(I7:J12)/12,IF(E9=M79,SUM(I7:J12)/6,IF(E9=M80,SUM(I7:J12)/4,IF(E9=M81,SUM(I7:J12)/2,IF(E9=M82,SUM(I7:J12),IF(E9=M83,SUM(I7:J12)*2,IF(E9=M84,SUM(I7:J12)*26/12,IF(E9=M85,SUM(I7:J12)*4,IF(E10=M78,SUM(I7:J12)/12,IF(E10=M79,SUM(I7:J12)/6,IF(E10=M80,SUM(I7:J12)/4,IF(E10=M81,SUM(I7:J12)/2,IF(E10=M82,SUM(I7:J12),IF(E10=M83,SUM(I7:J12)*2,IF(E10=M84,SUM(I7:J12)*26/12,IF(E10=M85,SUM(I7:J12)*4,IF(E11=M78,SUM(I7:J12)/12,IF(E11=M79,SUM(I7:J12)/6,IF(E11=M80,SUM(I7:J12)/4,IF(E11=M81,SUM(I7:J12)/2,IF(E11=M82,SUM(I7:J12),IF(E11=M83,SUM(I7:J12)*2,IF(E11=M84,SUM(I7:J12)*26/12,IF(E11=M85,SUM(I7:J12)*4,IF(E12=M78,SUM(I7:J12)/12,IF(E12=M79,SUM(I7:J12)/6,IF(E12=M80,SUM(I7:J12)/4,IF(E12=M81,SUM(I7:J12)/2,IF(E12=M82,SUM(I7:J12),IF(E12=M83,SUM(I7:J12)*2,IF(E12=M84,SUM(I7:J12)*26/12,IF(E12=M85,SUM(I7:J12)*4
))))))))))))))))))))))))))))))))))))))))))))))))

!!!!!!!!!!

**Mordred** · 08-01-2013, 11:11 PM

I think I just threw up a little into my mouth!

**FDibbins** · 08-02-2013, 12:00 AM

for giggles??

how would you ever edit that if you needed to add something, or is it wasnt giving what you expected?

For starters, you have a constant in there that could be used once instead of about 48 times...
SUM(I7:J12)/12,IF(E7=M79,
SUM(I7:J12)/6,IF(E7=M80,
SUM(I7:J12)/4,IF(E7=M81,
SUM(I7:J12)/2,IF(E7=M82,
SUM(I7:J12),IF(E7=M83,
SUM(I7:J12)*2,IF(E7=M84,
SUM(I7:J12)*26/12,IF(E7=M85,

depending on the value in E7/8/9 etc and M78/85, Im sure you could devise a formula that would give you a value to apply to the sum, without resorting to that monstrosity (and that would save Mordred's breakfast lol)

It's been my experience that despite how impressive and ""cool/wow" formulas like that look, they cause more problems than a series of simple helper columns...and in a LOT of cases, with a bit more thought, can be condensed by orders of magnitude to something far more manageable

**Pepe Le Mokko** · 08-02-2013, 05:33 AM

First THINK, THEN compute

**JapanDave** · 08-04-2013, 03:51 AM

I hate formulas and this just reinforces that.

**Tony Valko** · 08-04-2013, 09:28 PM

I've seen, and have probably written, longer formulas than that!

**FDibbins** · 08-05-2013, 12:10 AM

Likewise Tony, but you have to admit, there are almost always easier ways

**cytop** · 08-31-2013, 09:26 AM

Can't help this - even though it is off the board...

=TRIM(CONCATENATE(IFERROR(LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{10},{1}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Hundred","Two Hundred","Three Hundred","Four Hundred","Five Hundred","Six Hundred","Seven Hundred","Eight Hundred","Nine Hundred"}),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{1,2}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Crore","Twelve Crore","Thirteen Crore","Fourteen Crore","Fifteen Crore","Sixteen Crore","Seventeen Crore","Eighteen Crore","Nineteen Crore"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{1,2}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{8,9,10},{11,1,2}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{8,9,10,11},{11,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{12,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Crore","One Crore","Two Crore","Three Crore","Four Crore","Five Crore","Six Crore","Seven Crore","Eight Crore","Nine Crore"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Lac","Twelve Lac","Thirteen Lac","Fourteen Lac","Fifteen Lac","Sixteen Lac","Seventeen Lac","Eighteen Lac","Nineteen Lac"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{1,2,3,4}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{6,7,8,9,10},{11,1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{6,7,8,9,10},{11,2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{11,1,2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Lac","One Lac","Two Lac","Three Lac","Four Lac","Five Lac","Six Lac","Seven Lac","Eight Lac","Nine Lac"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten Thousand","Eleven Thousand","Twelve Thousand","Thirteen Thousand","Forteen Thousand","Fifteen Thousand","Sixteen Thousand","Seventeen Thousand","Eighteen Thousand","Nineteen Thousand"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{1,2,3,4,5,6}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="0",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Thousand","Two Thousand","Three Thousand","Four Thousand","Five Thousand","Six Thousand","Seven Thousand","Eight Thousand","Nine Thousand"}),IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A12,0),3,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Thousand","One Thousand","Two Thousand","Three Thousand","Four Thousand","Five Thousand","Six Thousand","Seven Thousand","Eight Thousand","Nine Thousand"}))),"")," ",IFERROR(LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Hundred","Two Hundred","Three Hundred","Four Hundred","Five Hundred","Six Hundred","Seven Hundred","Eight Hundred","Nine Hundred"}),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"})),"")," ","Rupee"," ",IFERROR(IF(LEN(FIND(".",A12))>0,"And",""),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,2)="1","Ten Paisa",""),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,1)="1",LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Paisa","Twelve Paisa","Thirteen Paisa","Fourteen Paisa","Fifteen Paisa","Sixteen Paisa","Seventeen Paisa","Eighteen Paisa","Nineteen Paisa"}),LOOKUP(MID(A12,FIND(".",A12)+1,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,1)="1",LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Paisa","Two Paisa","Three Paisa","Four Paisa","Five Paisa","Six Paisa","Seven Paisa","Eight Paisa","Nine Paisa"})),"")," ","Only."))

http://www.ozgrid.com/forum/showthre...494#post681494

**Tony Valko** · 08-31-2013, 10:25 AM

They had room for about 2000 more characters!

**FDibbins** · 08-31-2013, 10:43 AM

Tony, I dont even want to know how you figured that 1 out lol

**JapanDave** · 09-01-2013, 04:09 AM

So that formula trims some spaces... So what.

**Tony Valko** · 09-01-2013, 10:51 AM

I didn't goto the link (Ozgrid is one of the ugliest websites I've ever seen) but that formula looks like it's one of those "words to numbers/numbers to words" functions that are popular in Asia.

**JapanDave** · 09-01-2013, 10:27 PM

Originally Posted by Tony Valko

I didn't goto the link (Ozgrid is one of the ugliest websites I've ever seen) but that formula looks like it's one of those "words to numbers/numbers to words" functions that are popular in Asia.

Tony, I was just kidding. I started to get nauseated only 1/4 of the way through it, although it does work pretty good.

**lesoies** · 09-02-2013, 05:38 AM

Originally Posted by Tony Valko

...Ozgrid is one of the ugliest websites I've ever seen...

Is that strange thing to say? OzGrid and ExcelForum run same software and the even the color scheme is same.

Don't want start disagreement, just my comment

**romperstomper** · 09-02-2013, 06:27 AM

Ozgrid used to look very different. (as did VBAX, only more so!)

**My335iTT** · 09-09-2013, 11:35 PM

seems legit, hah

**lesoies** · 09-02-2013, 06:40 AM

Ahhh - see from WayBackMachine

but that change 3 year ago! Link is last old style page on wayback,

**Tony Valko** · 09-02-2013, 09:15 AM

Originally Posted by lesoies

Ahhh - see from WayBackMachine

but that change 3 year ago! Link is last old style page on wayback,

It was even uglier than that, believe it or not.

The design was terrible and the pages were crammed full of ads.

That's why I never participated there.

**daffodil11** · 09-03-2013, 04:03 PM

My personal best:

=(LEFT(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))-1)/MID(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))+1,LEN(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))-FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))))-(LEFT(VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0))-1)/MID(VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0))+1,LEN(VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0))-FIND(":",VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0))))

It vlookups a current month's values stored as text such as "75:80" (read: 75 out of 80), converts it to a fraction, vlookups the previous month's data, converts that, then then compares them.

I guess I'm still a rookie.

**romperstomper** · 09-04-2013, 03:35 AM

The phrase "helper cells" does spring to mind.

**daddylonglegs** · 09-04-2013, 09:12 AM

Rather than painfully extracting 75 and 80 from 75:80 you can use SUBSTITUTE to replace ":" with "/" and if you add "0 " to the front you get

0 75/80

which excel will recognise as a fraction if you co-erce it, so you can replace this part at the start

=(LEFT(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))-1)/MID(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))+1,LEN(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))-FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))))

with this:

=(0+("0 "&SUBSTITUTE(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0),":","/")))

**daffodil11** · 09-04-2013, 04:44 PM

You slay me, DLL.

Truncation has never been my strong suit. I will be sure to implement this in next week's report.

**daffodil11** · 09-10-2013, 06:06 PM

Someone needed a summary sheet of SUMIFs, pulling totals for each table from 52 worksheets.

It was a little painful.

=sumifs('Jan 6-8'!$M$5:$M$9,'Jan 6-8'!$L$5:$L$9,Summary!$C3)+sumifs('Jan 13-15'!$M$5:$M$9,'Jan 13-15'!$L$5:$L$9,Summary!$C3)+sumifs('Jan 20-22'!$M$5:$M$9,'Jan 20-22'!$L$5:$L$9,Summary!$C3)+sumifs('Jan 27-29'!$M$5:$M$9,'Jan 27-29'!$L$5:$L$9,Summary!$C3)+sumifs('Feb 3-5'!$M$5:$M$9,'Feb 3-5'!$L$5:$L$9,Summary!$C3)+sumifs('Feb 10-12'!$M$5:$M$9,'Feb 10-12'!$L$5:$L$9,Summary!$C3)+sumifs('Feb 17-19'!$M$5:$M$9,'Feb 17-19'!$L$5:$L$9,Summary!$C3)+sumifs('Feb 24-26'!$M$5:$M$9,'Feb 24-26'!$L$5:$L$9,Summary!$C3)+sumifs('Mar 2-4'!$M$5:$M$9,'Mar 2-4'!$L$5:$L$9,Summary!$C3)+sumifs('Mar 9-11'!$M$5:$M$9,'Mar 9-11'!$L$5:$L$9,Summary!$C3)+sumifs('Mar 16-18'!$M$5:$M$9,'Mar 16-18'!$L$5:$L$9,Summary!$C3)+sumifs('Mar 23-25'!$M$5:$M$9,'Mar 23-25'!$L$5:$L$9,Summary!$C3)+sumifs('Mar 30-32'!$M$5:$M$9,'Mar 30-32'!$L$5:$L$9,Summary!$C3)+sumifs('Apr 6-8'!$M$5:$M$9,'Apr 6-8'!$L$5:$L$9,Summary!$C3)+sumifs('Apr 13-15'!$M$5:$M$9,'Apr 13-15'!$L$5:$L$9,Summary!$C3)+sumifs('Apr 20-22'!$M$5:$M$9,'Apr 20-22'!$L$5:$L$9,Summary!$C3)+sumifs('Apr 27-29'!$M$5:$M$9,'Apr 27-29'!$L$5:$L$9,Summary!$C3)+sumifs('May 4-6'!$M$5:$M$9,'May 4-6'!$L$5:$L$9,Summary!$C3)+sumifs('May 11-13'!$M$5:$M$9,'May 11-13'!$L$5:$L$9,Summary!$C3)+sumifs('May 18-20'!$M$5:$M$9,'May 18-20'!$L$5:$L$9,Summary!$C3)+sumifs('May 25-27'!$M$5:$M$9,'May 25-27'!$L$5:$L$9,Summary!$C3)+sumifs('Jun 1-3'!$M$5:$M$9,'Jun 1-3'!$L$5:$L$9,Summary!$C3)+sumifs('Jun 8-10'!$M$5:$M$9,'Jun 8-10'!$L$5:$L$9,Summary!$C3)+sumifs('Jun 15-17'!$M$5:$M$9,'Jun 15-17'!$L$5:$L$9,Summary!$C3)+sumifs('Jun 22-24'!$M$5:$M$9,'Jun 22-24'!$L$5:$L$9,Summary!$C3)+sumifs('Jun 29-31'!$M$5:$M$9,'Jun 29-31'!$L$5:$L$9,Summary!$C3)+sumifs('Jul 6-8'!$M$5:$M$9,'Jul 6-8'!$L$5:$L$9,Summary!$C3)+sumifs('Jul 13-15'!$M$5:$M$9,'Jul 13-15'!$L$5:$L$9,Summary!$C3)+sumifs('Jul 20-22'!$M$5:$M$9,'Jul 20-22'!$L$5:$L$9,Summary!$C3)+sumifs('Jul 27-29'!$M$5:$M$9,'Jul 27-29'!$L$5:$L$9,Summary!$C3)+sumifs('Aug 3-5'!$M$5:$M$9,'Aug 3-5'!$L$5:$L$9,Summary!$C3)+sumifs('Aug 10-12'!$M$5:$M$9,'Aug 10-12'!$L$5:$L$9,Summary!$C3)+sumifs('Aug 17-19'!$M$5:$M$9,'Aug 17-19'!$L$5:$L$9,Summary!$C3)+sumifs('Aug 24-26'!$M$5:$M$9,'Aug 24-26'!$L$5:$L$9,Summary!$C3)+sumifs('Aug 31-33'!$M$5:$M$9,'Aug 31-33'!$L$5:$L$9,Summary!$C3)+sumifs('Sep 7-9'!$M$5:$M$9,'Sep 7-9'!$L$5:$L$9,Summary!$C3)+sumifs('Sep 14-16'!$M$5:$M$9,'Sep 14-16'!$L$5:$L$9,Summary!$C3)+sumifs('Sep 21-23'!$M$5:$M$9,'Sep 21-23'!$L$5:$L$9,Summary!$C3)+sumifs('Sep 28-30'!$M$5:$M$9,'Sep 28-30'!$L$5:$L$9,Summary!$C3)+sumifs('Oct 5-7'!$M$5:$M$9,'Oct 5-7'!$L$5:$L$9,Summary!$C3)+sumifs('Oct 12-14'!$M$5:$M$9,'Oct 12-14'!$L$5:$L$9,Summary!$C3)+sumifs('Oct 19-21'!$M$5:$M$9,'Oct 19-21'!$L$5:$L$9,Summary!$C3)+sumifs('Oct 26-28'!$M$5:$M$9,'Oct 26-28'!$L$5:$L$9,Summary!$C3)+sumifs('Nov 2-4'!$M$5:$M$9,'Nov 2-4'!$L$5:$L$9,Summary!$C3)+sumifs('Nov 9-11'!$M$5:$M$9,'Nov 9-11'!$L$5:$L$9,Summary!$C3)+sumifs('Nov 16-18'!$M$5:$M$9,'Nov 16-18'!$L$5:$L$9,Summary!$C3)+sumifs('Nov 23-25'!$M$5:$M$9,'Nov 23-25'!$L$5:$L$9,Summary!$C3)+sumifs('Nov 30-32'!$M$5:$M$9,'Nov 30-32'!$L$5:$L$9,Summary!$C3)+sumifs('Dec 7-9'!$M$5:$M$9,'Dec 7-9'!$L$5:$L$9,Summary!$C3)+sumifs('Dec 14-16'!$M$5:$M$9,'Dec 14-16'!$L$5:$L$9,Summary!$C3)+sumifs('Dec 21-23'!$M$5:$M$9,'Dec 21-23'!$L$5:$L$9,Summary!$C3)+sumifs('Dec 28-30'!$M$5:$M$9,'Dec 28-30'!$L$5:$L$9,Summary!$C3)

What would have been a better way?

**Tony Valko** · 09-10-2013, 08:13 PM

Just giving it a quick look over...

You should be able to use a SUMPRODUCT(SUMIFS(INDIRECT formula.

Or, put a SUMIFS on each sheet in the same cell then do a sum of that cell across all the sheets.

**:) Sixthsense :)** · 09-11-2013, 12:12 AM

Why not sumif instead of sumifS?

Or

Go for VBA solution...

**Tony Valko** · 09-11-2013, 10:51 AM

Originally Posted by :) Sixthsense :)

Why not sumif instead of sumifS?

Upon closer inspection...

Yes, SUMIF.

**shg** · 09-10-2013, 06:19 PM

Redesign the workbook so that all of the data is on a single sheet.

**daffodil11** · 09-10-2013, 06:22 PM

Amen to that. If only they had provided a sample workbook.

Frownyface.

**Grimace** · 09-26-2013, 09:35 PM

I copied Cytops formula into a sheet and it gave me the answer .. "You are kidding arent you"

**Tony Valko** · 09-28-2013, 03:32 PM

This one is quite long but the best alternative is a relatively easy SUMPRODUCT function:

http://www.excelforum.com/excel-form...-too-long.html

**RGrunden** · 10-18-2013, 01:07 AM

It's from India, the giveaway is the word rupee about 2/3 of the way down. The Rupee is the currency of India (and Hyrule).

**Jacc** · 10-29-2013, 04:10 AM

=IFERROR(IF(ISTEXT(RIGHT(MID(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));FIND("@";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));"BILL NO.";"@");"PP NO.";"@");"RECEPT NO.";"@"));13);1));MID(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));FIND("@";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));"BILL NO.";"@");"PP NO.";"@");"RECEPT NO.";"@"));12);MID(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));FIND("@";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));"BILL NO.";"@");"PP NO.";"@");"RECEPT NO.";"@"));13));"")

This one was posted recently. Alt + Enter before every INDEX reveals a pattern. I made named formulas of that and thought I done something smart until I realized the ROWS($A$1:A1). No dollars, no named formulas or? Anyway, I gave up.
http://www.excelforum.com/excel-form...t-formula.html

**Jacc** · 11-20-2013, 05:31 PM

http://www.excelforum.com/excel-form...scheduler.html

**Tony Valko** · 06-21-2014, 08:04 PM

This may be the longest formula I've ever seen.

http://www.excelforum.com/excel-form...f-nesting.html

**FDibbins** · 06-21-2014, 08:16 PM

Yup, Im sure that must be some sort of record Tony...kudo's to the OP though for creating it

**TMS** · 07-26-2014, 04:29 AM

Maybe this: http://www.excelforum.com/review/102...ml#post3781660

Formula:



=IF(E2=0,0,IF(AND($A2=$A1,E1>0),0,IF(AND($A3=$A2,E3>0,OR($A3<>$A4,E4=0)),SUM(E2:E3),IF(AND($A3=$A2,$A4=$A2,E3>0,E4>0,OR($A4<>$A5,E5=0)),SUM(E2:E4),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,E3>0,E4>0,E5>0,OR($A5<>$A6,E6=0)),SUM(E2:E5),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,E3>0,E4>0,E5>0,E6>0,OR($A6<>$A7,E7=0)),SUM(E2:E6),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,OR($A7<>$A8,E8=0)),SUM(E2:E7),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,OR($A8<>$A9,E9=0)),SUM(E2:E8),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,OR($A9<>$A10,E10=0)),SUM(E2:E9),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,OR($A10<>$A11,E11=0)),SUM(E2:E10),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,OR($A11<>$A12,E12=0)),SUM(E2:E11),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,OR($A12<>$A13,E13=0)),SUM(E2:E12),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,OR($A13<>$A14,E14=0)),SUM(E2:E13),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,OR($A14<>$A15,E15=0)),SUM(E2:E14),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,OR($A15<>$A16,E16=0)),SUM(E2:E15),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,OR($A16<>$A17,E17=0)),SUM(E2:E16),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,OR($A17<>$A18,E18=0)),SUM(E2:E17),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,$A18=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,E18>0,OR($A18<>$A19,E19=0)),SUM(E2:E18),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,$A18=$A2,$A19=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,E18>0,E19>0,OR($A19<>$A20,E20=0)),SUM(E2:E19),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,$A18=$A2,$A19=$A2,$A20=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,E18>0,E19>0,E20>0,OR($A20<>$A21,E21=0)),SUM(E2:E20),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,$A18=$A2,$A19=$A2,$A20=$A2,$A21=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,E18>0,E19>0,E20>0,E21>0,OR($A21<>$A22,E22=0)),SUM(E2:E21),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,$A18=$A2,$A19=$A2,$A20=$A2,$A21=$A2,$A22=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,E18>0,E19>0,E20>0,E21>0,E22>0),SUM(E2:E22),E2))))))))))))))))))))))

Regards, TMS

**Tony Valko** · 07-26-2014, 09:26 AM

Are you sure that's the correct link?

**FDibbins** · 02-19-2015, 09:09 PM

I have 1 here that excel says is too long - exceeds 8192 characters...
http://www.excelforum.com/excel-form...reference.html

=IF(R20="Wholesale", IF(AND(C20=B134),INDEX(OFFSET('Prices-W'!$B$5:$R$14,(0+(J20-1))*14,0),IF(L20<'Prices-W'!$A$5,0,MATCH(L20,'Prices-W'!$A$5:$A$14))+(COUNTIF('Prices-W'!$A$5:$A$14,L20)=0),IF(K20<'Prices-W'!$B$4,0,MATCH(K20,'Prices-W'!$B$4:$R$4))+(COUNTIF('Prices-W'!$B$4:$R$4,K20)=0)),IF(AND(C20=B135,J20<6),INDEX(OFFSET('Prices-W'!$B$105:$AK$119,(0+(J20-1))*19,0),IF(L20<'Prices-W'!$A$105,0,MATCH(L20,'Prices-W'!$A$105:$A$119))+(COUNTIF('Prices-W'!$A$105:$A$119,L20)=0),IF(K20<'Prices-W'!$B$104,0,MATCH(K20,'Prices-W'!$B$104:$AK$104))+(COUNTIF('Prices-W'!$B$104:$AK$104,K20)=0)),IF(AND(C20=B136,J20<6),INDEX(OFFSET('Prices-W'!$B$202:$AK$216,(0+(J20-1))*19,0),IF(L20<'Prices-W'!$A$202,0,MATCH(L20,'Prices-W'!$A$202:$A$216))+(COUNTIF('Prices-W'!$A$202:$A$216,L20)=0),IF(K20<'Prices-W'!$B$201,0,MATCH(K20,'Prices-W'!$B$201:$AK$201))+(COUNTIF('Prices-W'!$B$201:$AK$201,K20)=0)),IF(AND(C20=B137,J20<7),INDEX(OFFSET('Prices-W'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-W'!$A$299,0,MATCH(L20,'Prices-W'!$A$299:$A$307))+(COUNTIF('Prices-W'!$A$299:$A$307,L20)=0),IF(K20<'Prices-W'!$B$298,0,MATCH(K20,'Prices-W'!$B$298:$P$298))+(COUNTIF('Prices-W'!$B$298:$P$298,K20)=0)),IF(AND(OR(C20=B138,C20=B139),J20<7),1.3*(INDEX(OFFSET('Prices-W'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-W'!$A$299,0,MATCH(L20,'Prices-W'!$A$299:$A$307))+(COUNTIF('Prices-W'!$A$299:$A$307,L20)=0),IF(K20<'Prices-W'!$B$298,0,MATCH(K20,'Prices-W'!$B$298:$P$298))+(COUNTIF('Prices-W'!$B$298:$P$298,K20)=0))),IF(AND(C20=B141),INDEX(OFFSET('Prices-W'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-W'!$A$379,0,MATCH(L20,'Prices-W'!$A$379:$A$391))+(COUNTIF('Prices-W'!$A$379:$A$391,L20)=0),IF(K20<'Prices-W'!$B$378,0,MATCH(K20,'Prices-W'!$B$378:$P$378))+(COUNTIF('Prices-W'!$B$378:$P$378,K20)=0)),IF(AND(C20=B140),1.6*(INDEX(OFFSET('Prices-W'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-W'!$A$379,0,MATCH(L20,'Prices-W'!$A$379:$A$391))+(COUNTIF('Prices-W'!$A$379:$A$391,L20)=0),IF(K20<'Prices-W'!$B$378,0,MATCH(K20,'Prices-W'!$B$378:$P$378))+(COUNTIF('Prices-W'!$B$378:$P$378,K20)=0))),IF(C20=B143,IF(L20<'Prices-W'!$A$500,0,MATCH(L20,'Prices-W'!$A$500:$A$526))+(COUNTIF('Prices-W'!$A$500:$A$526,L20)=0),IF(K20<'Prices-W'!$B$499,0,MATCH(K20,'Prices-W'!$B$499:$W$499))+(COUNTIF('Prices-W'!$B$499:$W$499,K20)=0),IF(C20=B144,IF(L20<'Prices-W'!$A$532,0,MATCH(L20,'Prices-W'!$A$532:$A$558))+(COUNTIF('Prices-W'!$A$532:$A$558,L20)=0),IF(K20<'Prices-W'!$B$531,0,MATCH(K20,'Prices-W'!$B$531:$W$531))+(COUNTIF('Prices-W'!$B$531:$W$531,K20)=0),IF(C20=B145,IF(L20<'Prices-W'!$A$564,0,MATCH(L20,'Prices-W'!$A$564:$A$590))+(COUNTIF('Prices-W'!$A$564:$A$590,L20)=0),IF(K20<'Prices-W'!$B$563,0,MATCH(K20,'Prices-W'!$B$563:$Z$563))+(COUNTIF('Prices-W'!$B$563:$Z$563,K20)=0),IF(C20=B146,IF(L20<'Prices-W'!$A$596,0,MATCH(L20,'Prices-W'!$A$596:$A$620))+(COUNTIF('Prices-W'!$A$596:$A$620,L20)=0),IF(K20<'Prices-W'!$B$595,0,MATCH(K20,'Prices-W'!$B$595:$U$595))+(COUNTIF('Prices-W'!$B$595:$U$595,K20)=0),IF(C20=B147,IF(L20<'Prices-W'!$A$626,0,MATCH(L20,'Prices-W'!$A$626:$A$652))+(COUNTIF('Prices-W'!$A$626:$A$652,L20)=0),IF(K20<'Prices-W'!$B$625,0,MATCH(K20,'Prices-W'!$B$625:$W$625))+(COUNTIF('Prices-W'!$B$625:$W$625,K20)=0),IF(C20=B148,IF(L20<'Prices-W'!$A$658,0,MATCH(L20,'Prices-W'!$A$658:$A$684))+(COUNTIF('Prices-W'!$A$658:$A$684,L20)=0),IF(K20<'Prices-W'!$B$657,0,MATCH(K20,'Prices-W'!$B$657:$W$657))+(COUNTIF('Prices-W'!$B$657:$W$657,K20)=0),IF(C20=B142,(((K20*L20)/1000000)*290)+(G20*'Prices-W'!$D$688)+(H20*'Prices-W'!$C$689),IF(C20=B149,IF(L20<'Prices-W'!$A$695,0,MATCH(L20,'Prices-W'!$A$695:$A$696))+(COUNTIF('Prices-W'!$A$695:$A$696,L20)=0),IF(K20<'Prices-W'!$B$694,0,MATCH(K20,'Prices-W'!$B$694:$O$694))+(COUNTIF('Prices-W'!$B$694:$O$694,K20)=0),IF(C20=B150,IF(L20<'Prices-W'!$A$702,0,MATCH(L20,'Prices-W'!$A$702:$A$703))+(COUNTIF('Prices-W'!$A$702:$A$703,L20)=0),IF(K20<'Prices-W'!$B$701,0,MATCH(K20,'Prices-W'!$B$701:$O$701))+(COUNTIF('Prices-W'!$B$701:$O$701,K20)=0),IF(C20=B151,IF(L20<'Prices-W'!$A$709,0,MATCH(L20,'Prices-W'!$A$709:$A$710))+(COUNTIF('Prices-W'!$A$709:$A$710,L20)=0),IF(K20<'Prices-W'!$B$708,0,MATCH(K20,'Prices-W'!$B$708:$O$708))+(COUNTIF('Prices-W'!$B$708:$O$708,K20)=0)," ")))))))))))))))))),IF(R20="Retail", IF(AND(C20=B134),INDEX(OFFSET('Prices-R'!$B$5:$R$14,(0+(J20-1))*14,0),IF(L20<'Prices-R'!$A$5,0,MATCH(L20,'Prices-R'!$A$5:$A$14))+(COUNTIF('Prices-R'!$A$5:$A$14,L20)=0),IF(K20<'Prices-R'!$B$4,0,MATCH(K20,'Prices-R'!$B$4:$R$4))+(COUNTIF('Prices-R'!$B$4:$R$4,K20)=0)),IF(AND(C20=B135,J20<6),INDEX(OFFSET('Prices-R'!$B$105:$AK$119,(0+(J20-1))*19,0),IF(L20<'Prices-R'!$A$105,0,MATCH(L20,'Prices-R'!$A$105:$A$119))+(COUNTIF('Prices-R'!$A$105:$A$119,L20)=0),IF(K20<'Prices-R'!$B$104,0,MATCH(K20,'Prices-R'!$B$104:$AK$104))+(COUNTIF('Prices-R'!$B$104:$AK$104,K20)=0)),IF(AND(C20=B136,J20<6),INDEX(OFFSET('Prices-R'!$B$202:$AK$216,(0+(J20-1))*19,0),IF(L20<'Prices-R'!$A$202,0,MATCH(L20,'Prices-R'!$A$202:$A$216))+(COUNTIF('Prices-R'!$A$202:$A$216,L20)=0),IF(K20<'Prices-R'!$B$201,0,MATCH(K20,'Prices-R'!$B$201:$AK$201))+(COUNTIF('Prices-R'!$B$201:$AK$201,K20)=0)),IF(AND(C20=B137,J20<7),INDEX(OFFSET('Prices-R'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-R'!$A$299,0,MATCH(L20,'Prices-R'!$A$299:$A$307))+(COUNTIF('Prices-R'!$A$299:$A$307,L20)=0),IF(K20<'Prices-R'!$B$298,0,MATCH(K20,'Prices-R'!$B$298:$P$298))+(COUNTIF('Prices-R'!$B$298:$P$298,K20)=0)),IF(AND(OR(C20=B138,C20=B139),J20<7),1.3*(INDEX(OFFSET('Prices-R'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-R'!$A$299,0,MATCH(L20,'Prices-R'!$A$299:$A$307))+(COUNTIF('Prices-R'!$A$299:$A$307,L20)=0),IF(K20<'Prices-R'!$B$298,0,MATCH(K20,'Prices-R'!$B$298:$P$298))+(COUNTIF('Prices-R'!$B$298:$P$298,K20)=0))),IF(AND(C20=B141),INDEX(OFFSET('Prices-R'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-R'!$A$379,0,MATCH(L20,'Prices-R'!$A$379:$A$391))+(COUNTIF('Prices-R'!$A$379:$A$391,L20)=0),IF(K20<'Prices-R'!$B$378,0,MATCH(K20,'Prices-R'!$B$378:$P$378))+(COUNTIF('Prices-R'!$B$378:$P$378,K20)=0)),IF(AND(C20=B140),1.6*(INDEX(OFFSET('Prices-R'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-R'!$A$379,0,MATCH(L20,'Prices-R'!$A$379:$A$391))+(COUNTIF('Prices-R'!$A$379:$A$391,L20)=0),IF(K20<'Prices-R'!$B$378,0,MATCH(K20,'Prices-R'!$B$378:$P$378))+(COUNTIF('Prices-R'!$B$378:$P$378,K20)=0))),IF(C20=B143,IF(L20<'Prices-R'!$A$500,0,MATCH(L20,'Prices-R'!$A$500:$A$526))+(COUNTIF('Prices-R'!$A$500:$A$526,L20)=0),IF(K20<'Prices-R'!$B$499,0,MATCH(K20,'Prices-R'!$B$499:$W$499))+(COUNTIF('Prices-R'!$B$499:$W$499,K20)=0),IF(C20=B144,IF(L20<'Prices-R'!$A$532,0,MATCH(L20,'Prices-R'!$A$532:$A$558))+(COUNTIF('Prices-R'!$A$532:$A$558,L20)=0),IF(K20<'Prices-R'!$B$531,0,MATCH(K20,'Prices-R'!$B$531:$W$531))+(COUNTIF('Prices-R'!$B$531:$W$531,K20)=0),IF(C20=B145,IF(L20<'Prices-R'!$A$564,0,MATCH(L20,'Prices-R'!$A$564:$A$590))+(COUNTIF('Prices-R'!$A$564:$A$590,L20)=0),IF(K20<'Prices-R'!$B$563,0,MATCH(K20,'Prices-R'!$B$563:$Z$563))+(COUNTIF('Prices-R'!$B$563:$Z$563,K20)=0),IF(C20=B146,IF(L20<'Prices-R'!$A$596,0,MATCH(L20,'Prices-R'!$A$596:$A$620))+(COUNTIF('Prices-R'!$A$596:$A$620,L20)=0),IF(K20<'Prices-R'!$B$595,0,MATCH(K20,'Prices-R'!$B$595:$U$595))+(COUNTIF('Prices-R'!$B$595:$U$595,K20)=0),IF(C20=B147,IF(L20<'Prices-R'!$A$626,0,MATCH(L20,'Prices-R'!$A$626:$A$652))+(COUNTIF('Prices-R'!$A$626:$A$652,L20)=0),IF(K20<'Prices-R'!$B$625,0,MATCH(K20,'Prices-R'!$B$625:$W$625))+(COUNTIF('Prices-R'!$B$625:$W$625,K20)=0),IF(C20=B148,IF(L20<'Prices-R'!$A$658,0,MATCH(L20,'Prices-R'!$A$658:$A$684))+(COUNTIF('Prices-R'!$A$658:$A$684,L20)=0),IF(K20<'Prices-R'!$B$657,0,MATCH(K20,'Prices-R'!$B$657:$W$657))+(COUNTIF('Prices-R'!$B$657:$W$657,K20)=0),IF(C20=B142,(((K20*L20)/1000000)*290)+(G20*'Prices-R'!$D$688)+(H20*'Prices-R'!$C$689),IF(C20=B149,IF(L20<'Prices-R'!$A$695,0,MATCH(L20,'Prices-R'!$A$695:$A$696))+(COUNTIF('Prices-R'!$A$695:$A$696,L20)=0),IF(K20<'Prices-R'!$B$694,0,MATCH(K20,'Prices-R'!$B$694:$O$694))+(COUNTIF('Prices-R'!$B$694:$O$694,K20)=0),IF(C20=B150,IF(L20<'Prices-R'!$A$702,0,MATCH(L20,'Prices-R'!$A$702:$A$703))+(COUNTIF('Prices-R'!$A$702:$A$703,L20)=0),IF(K20<'Prices-R'!$B$701,0,MATCH(K20,'Prices-R'!$B$701:$O$701))+(COUNTIF('Prices-R'!$B$701:$O$701,K20)=0),IF(C20=B151,IF(L20<'Prices-R'!$A$709,0,MATCH(L20,'Prices-R'!$A$709:$A$710))+(COUNTIF('Prices-R'!$A$709:$A$710,L20)=0),IF(K20<'Prices-R'!$B$708,0,MATCH(K20,'Prices-R'!$B$708:$O$708))+(COUNTIF('Prices-R'!$B$708:$O$708,K20)=0)," "))))))))))))))))),"N/A"))

**Butcher1** · 02-26-2015, 10:43 AM

=IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=1,SUM(INDEX((INDEX(Ex_Data,0,MATCH(VLOOKUP($A74,Time_Sheet_Lookup_Table,3,FALSE),Ex_Data[#Headers],FALSE)))*(Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Ex_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Ex_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Ex_Data[[Day]:[Day]]=C$68),1)))),)),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=11,SUM(INDEX((INDEX(Day_Data,0,MATCH(VLOOKUP($A74,Time_Sheet_Lookup_Table,3,FALSE),Day_Data[#Headers],FALSE)))*(Day_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Day_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Day_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Day_Data[[Day]:[Day]]=C$68),1)))),)),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=2,MAX(INDEX((INDEX(Ex_Data,0,MATCH(VLOOKUP($A74,Time_Sheet_Lookup_Table,3,FALSE),Ex_Data[#Headers],FALSE)))*(Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Ex_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Ex_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Ex_Data[[Day]:[Day]]=C$68),1)))),)),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=3,MIN(INDEX((INDEX(Ex_Data,0,MATCH(VLOOKUP($A74,Time_Sheet_Lookup_Table,3,FALSE),Ex_Data[#Headers],FALSE)))*(Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Ex_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Ex_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Ex_Data[[Day]:[Day]]=C$68),1)))),)),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=4,SUM(IF(C$65="",1,Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)*IF(C$66="",1,Ex_Data[[Mesocycle]:[Mesocycle]]=C$66)*IF(C$67="",1,Ex_Data[[Week]:[Week]]=C$67)*IF(C$68="",1,Ex_Data[[Day]:[Day]]=C$68)),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=5,MROUND(SUMPRODUCT(Protocol_Rep,C_Col_Protocol_Rep)/SUM(C_Col_Protocol_Rep),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=51,MROUND(AVERAGE(INDEX((INDEX(Day_Data,0,MATCH(VLOOKUP($A74,Time_Sheet_Lookup_Table,3,FALSE),Day_Data[#Headers],FALSE)))*(Day_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Day_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Day_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Day_Data[[Day]:[Day]]=C$68),1)))),)),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=511,MROUND(SUMPRODUCT(Scale_1_To_5,C_Col_Session_RPE)/SUM(C_Col_Session_RPE),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=512,MROUND(SUMPRODUCT(Scale_1_To_5,C_Col_Readiness_Rating)/SUM(C_Col_Readiness_Rating),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=513,MROUND(SUMPRODUCT(Scale_1_To_5,C_Col_Quality_of_Session)/SUM(C_Col_Quality_of_Session),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=52,MROUND(SUMPRODUCT(Protocol_RI,C_Col_Protocol_RI)/SUM(C_Col_Protocol_RI),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=53,MROUND(SUMPRODUCT(Protocol_RPE,C_Col_Protocol_RPE)/SUM(C_Col_Protocol_RPE),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=6,MROUND(SUMPRODUCT(RT_Values,C_Col_Avg_RT)/SUM(C_Col_Avg_RT),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=7,MROUND(SUMPRODUCT(Intensity_Values,C_Col_Avg_Int)/SUM(C_Col_Avg_Int),0.01),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=8,MROUND(SUMPRODUCT(RPE_Values,C_Col_Avg_RPE)/SUM(C_Col_Avg_RPE),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=9,MROUND(SUM(INDEX((INDEX(Ex_Data,0,27))*(Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Ex_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Ex_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Ex_Data[[Day]:[Day]]=C$68),1)))),))*SUMPRODUCT(Intensity_Values,C_Col_Avg_Int)/SUM(C_Col_Avg_Int),1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=10,SUMPRODUCT(((Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)+(C$65=""))*((Ex_Data[[Mesocycle]:[Mesocycle]]=C$66)+(C$66=""))*((Ex_Data[[Week]:[Week]]=C$67)+(C$67=""))*((Ex_Data[[Day]:[Day]]=C$68)+(C$68=""))*ISNUMBER(SEARCH(VLOOKUP($A74,PR_Lookup_Table,3,FALSE),IF(OR($A74="All Time PRs, est.",$A74="All Time PRs, missed",$A74="All Time PRs, new"),Ex_Data[[All Time PR]:[All Time PR]],Ex_Data[[Protocol PR]:[Protocol PR]])))))))))))))))))))))

4086 characters not counting the curly brackets. Basically it looks up a row label and returns the data that corresponds with the appropriate time unit column labels.

**Speshul** · 02-26-2015, 04:31 PM

My personal longest to date, only 3060, and I never got a chance to use it. I lost the thread I was responding to

I made it months ago and have no idea what it does anymore :D

=IFERROR(SUM(CHOOSE(MATCH(N$14,$C$1:$N$1,0),,CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0)
,SUM('Tab1'!$B5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$C5),SUM('Tab1'!
$B5:$C5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$D5),SUM('Tab1'!$C5:$D5)
,SUM('Tab1'!$B5:$D5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$E5),SUM(
'Tab1'!$D5:$E5),SUM('Tab1'!$C5:$E5),SUM('Tab1'!$B5:$E5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)
+1,FALSE),0),SUM('Tab1'!$F5),SUM('Tab1'!$E5:$F5),SUM('Tab1'!$D5:$F5),SUM('Tab1'!$C5:$F5),SUM('Tab1'!$B5:$F5)),CHOOSE(ROUNDDOWN(VLOOKUP
($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$G5),SUM('Tab1'!$F5:$G5),SUM('Tab1'!$E5:$G5),SUM(
'Tab1'!$D5:$G5),SUM('Tab1'!$C5:$G5),SUM('Tab1'!$B5:$G5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)
+1,FALSE),0),SUM('Tab1'!$H5),SUM('Tab1'!$G5:$H5),SUM('Tab1'!$F5:$H5),SUM('Tab1'!$E5:$H5),SUM('Tab1'!$D5:$H5),SUM('Tab1'!$C5:$H5),SUM
('Tab1'!$B5:$H5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$I5),SUM(
'Tab1'!$H5:$I5),SUM('Tab1'!$G5:$I5),SUM('Tab1'!$F5:$I5),SUM('Tab1'!$E5:$I5),SUM('Tab1'!$D5:$I5),SUM('Tab1'!$C5:$I5),SUM('Tab1'!$B5:$I5)
)*CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$J5),SUM('Tab1'!$I5:$J5),SUM
('Tab1'!$H5:$J5),SUM('Tab1'!$G5:$J5),SUM('Tab1'!$F5:$J5),SUM('Tab1'!$E5:$J5),SUM('Tab1'!$D5:$J5),SUM('Tab1'!$C5:$J5),SUM('Tab1'!$B5:$J5)
),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$K5),SUM('Tab1'!$J5:$K5),SUM
('Tab1'!$J5:$K5),SUM('Tab1'!$I5:$K5),SUM('Tab1'!$H5:$K5),SUM('Tab1'!$G5:$K5),SUM('Tab1'!$F5:$K5),SUM('Tab1'!$E5:$K5),SUM('Tab1'!$D5:$K5)
,SUM('Tab1'!$C5:$K5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$L5),SUM
('Tab1'!$K5:$L5),SUM('Tab1'!$J5:$L5),SUM('Tab1'!$I5:$L5),SUM('Tab1'!$H5:$L5),SUM('Tab1'!$G5:$L5),SUM('Tab1'!$F5:$L5),SUM('Tab1'!$E5:$L5)
,SUM('Tab1'!$D5:$L5),SUM('Tab1'!$C5:$L5),SUM('Tab1'!$B5:$L5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)
+1,FALSE),0),SUM('Tab1'!$M5),SUM('Tab1'!$L5:$M5),SUM('Tab1'!$K5:$M5),SUM('Tab1'!$J5:$M5),SUM('Tab1'!$I5:$M5),SUM('Tab1'!$H5:$M5),SUM
('Tab1'!$G5:$M5),SUM('Tab1'!$F5:$M5),SUM('Tab1'!$E5:$M5),SUM('Tab1'!$D5:$M5),SUM('Tab1'!$C5:$M5),SUM('Tab1'!$B5:$M5))),CHOOSE(ROUNDDOWN
(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),0)+COLUMNS(N:$N),'Tab1'!$M5,'Tab1'!$L5,'Tab1'!$K5,
'Tab1'!$J5,'Tab1'!$I5,'Tab1'!$H5,'Tab1'!$G5,'Tab1'!$F5,'Tab1'!$E5,'Tab1'!$D5,'Tab1'!$C5,'Tab1'!$B5)*(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE)-ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0))),"0")

**Tony Valko** · 02-17-2016, 06:45 PM

This one might take the top prize...

https://www.excelforum.com/showthread.php?p=4320170

Hybrid View

Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Re: Longest Formula Ever

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions