Array Error #N/A

[ExcelRoy]

Hi,

Could some kind person modify my array formula to show no errors

I need the formula to be a few hundred lines but all lines have not had adta in them yet

The formula is

=INDEX('Product Entry'!$C$8:$C$20,MATCH(MAX(IF(1-ISNUMBER(MATCH('Product Entry'!$C$8:$C$20,#REF!,0)),SUMIF('Product Entry'!$C$8:$C$20,'Product Entry'!$C$8:$C$20,'Product Entry'!$G$8:$G$20))),IF(1-ISNUMBER(MATCH('Product Entry'!$C$8:$C$20,#REF!,0)),SUMIF('Product Entry'!$C$8:$C$20,'Product Entry'!$C$8:$C$20,'Product Entry'!$G$8:$G$20)),0))

Thanks

 

[Aladin Akyurek]

Hi,

Could some kind person modify my array formula to show no errors

I need the formula to be a few hundred lines but all lines have not had adta in them yet

The formula is

=INDEX('Product Entry'!$C$8:$C$20,MATCH(MAX(IF(1-ISNUMBER(MATCH('Product Entry'!$C$8:$C$20,#REF!,0)),SUMIF('Product Entry'!$C$8:$C$20,'Product Entry'!$C$8:$C$20,'Product Entry'!$G$8:$G$20))),IF(1-ISNUMBER(MATCH('Product Entry'!$C$8:$C$20,#REF!,0)),SUMIF('Product Entry'!$C$8:$C$20,'Product Entry'!$C$8:$C$20,'Product Entry'!$G$8:$G$20)),0))

Thanks

Roy

What is your Excel version? Note that the formula shows #REF! bits...

 

[ExcelRoy]

Hi Aladin,

Thanks, im gonna have a look into the REF error

Thanks

 

[ExcelRoy]

Hi Aladin,

Pleas find below my revised array formula

=INDEX('Product Entry'!$C$8:$C$20,MATCH(MAX(IF(1-ISNUMBER(MATCH('Product Entry'!$C$8:$C$20,$C$7:$C7,0)),SUMIF('Product Entry'!$C$8:$C$20,'Product Entry'!$C$8:$C$20,'Product Entry'!$G$8:$G$20))),IF(1-ISNUMBER(MATCH('Product Entry'!$C$8:$C$20,$C$7:$C7,0)),SUMIF('Product Entry'!$C$8:$C$20,'Product Entry'!$C$8:$C$20,'Product Entry'!$G$8:$G$20)),0))

I have Excel 2007 at home but need it to work on Excel 2003 at work

Thanks

 

[Aladin Akyurek]

Hi Aladin,

Pleas find below my revised array formula

=INDEX('Product Entry'!$C$8:$C$20,MATCH(MAX(IF(1-ISNUMBER(MATCH('Product Entry'!$C$8:$C$20,$C$7:$C7,0)),SUMIF('Product Entry'!$C$8:$C$20,'Product Entry'!$C$8:$C$20,'Product Entry'!$G$8:$G$20))),IF(1-ISNUMBER(MATCH('Product Entry'!$C$8:$C$20,$C$7:$C7,0)),SUMIF('Product Entry'!$C$8:$C$20,'Product Entry'!$C$8:$C$20,'Product Entry'!$G$8:$G$20)),0))

I have Excel 2007 at home but need it to work on Excel 2003 at work

Thanks

Are you sure that this formula ends up in an error value, unless the ranges it references have such a value?

By the way, what does this formula do?

 

[ExcelRoy]

Hi Aladin,

The formula has an accumalative total for a particular "Job" or Product

ie. if Job123 has 4 different lines of Product A then this formula shows "4" etc

Until criteria is met the formula shows #N/A, ideally i would like it to show ""

The next column has =SUMIF('Product Entry'!$C$8:$C$20,C8,'Product Entry'!$G$8:$G$20)


I Used this thread as a base to work with this formula
http://www.mrexcel.com/forum/showthread.php?t=477233&highlight=RANK+TEXT

Thanks

 

[Aladin Akyurek]

Hi Aladin,

The formula has an accumalative total for a particular "Job" or Product

ie. if Job123 has 4 different lines of Product A then this formula shows "4" etc

Until criteria is met the formula shows #N/A, ideally i would like it to show ""

The next column has =SUMIF('Product Entry'!$C$8:$C$20,C8,'Product Entry'!$G$8:$G$20)


I Used this thread as a base to work with this formula
http://www.mrexcel.com/forum/showthread.php?t=477233&highlight=RANK+TEXT

Thanks

Ok...

Add the following VBA code to your workbook...

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

C6, control+shift+enter:

=SUM(IF(FREQUENCY(IF('Product Entry'!$C$8:$C$20<>"",MATCH("~"&'Product Entry'!$C$8:$C$20,'Product Entry'!$C$8:$C$20&"",0)),ROW('Product Entry'!$C$8:$C$20)-ROW('Product Entry'!$C$8)+1),1))

I assume we have in C7 something like #List#

C8, control+shift+enter and copy down...

=IF(ROWS($F$7:F7)<=$F$6,INDEX('Product Entry'!$C$8:$C$20,MATCH(MAX(v(IF(1-ISNUMBER(MATCH('Product Entry'!$C$8:$C$20,$C$7:$C7,0)),SUMIF('Product Entry'!$C$8:$C$20,'Product Entry'!$C$8:$C$20,'Product Entry'!$G$8:$G$20)))),v(),0)),"")

I expect that this will also run on Excel 2003.