sumproduct with errors in the columns

[benwork]

Hi all

Im using this forumla

=IF(C4>0,SUMPRODUCT(--(output!$H$5:$H$8205='Stockpile Totals'!A4),(output!$C$5:$C$8205),output!$D$5:$D$8205)/C4,"")

Although I have some errors in the output!$H$5:$H$8205 columns and it gives me an error.

Is there a way I can ignore those errors in the output!$H$5:$H$8205 for the forumla to work?

Cheers

 

[T. Valko]

Hi all

Im using this forumla

=IF(C4>0,SUMPRODUCT(--(output!$H$5:$H$8205='Stockpile Totals'!A4),(output!$C$5:$C$8205),output!$D$5:$D$8205)/C4,"")

Although I have some errors in the output!$H$5:$H$8205 columns and it gives me an error.

Is there a way I can ignore those errors in the output!$H$5:$H$8205 for the forumla to work?

Cheers

What type of data is in output!$H$5:$H$8205? Is it text? Numeric? Could be both?

What version of Excel are you using?

 

[Aladin Akyurek]

Hi all

Im using this forumla

=IF(C4>0,SUMPRODUCT(--(output!$H$5:$H$8205='Stockpile Totals'!A4),(output!$C$5:$C$8205),output!$D$5:$D$8205)/C4,"")

Although I have some errors in the output!$H$5:$H$8205 columns and it gives me an error.

Is there a way I can ignore those errors in the output!$H$5:$H$8205 for the forumla to work?

Cheers

Try...

=IF(C4>0,SUMPRODUCT(--ISNUMBER(MATCH(output!$H$5:$H$8205,'Stockpile Totals'!A4,0)),output!$C$5:$C$8205,output!$D$5:$D$8205)/C4,"")

 

[benwork]

Text based and 2007

 

[T. Valko]

Text based and 2007

Try this...

=IF(C4>0,SUMPRODUCT(--(ISNUMBER(MATCH(output!$H$5:$H$8205,'Stockpile Totals'!A4,0))),output!$C$5:$C$8205,output!$D$5:$D$8205)/C4,"")

 

[benwork]

Thanks team

Worked perfectly

Cheers