SUMPRODUCT help to ignore #NA and #Value

[mecheet]

I have the below formula which works when i remove the #NA and #Values in column F on the RPT tab, but i need it to just ignore these.

=SUMPRODUCT((RPT!$F$6:$F$2793=$A8)*(RPT!CV$6:CV$2793)*(RPT!$M$6:$M$2793=Overview!$B8))/$H$3

any help appreciated

 

[yytsunamiyy]

try:

=SUMPRODUCT(--(ISERROR((RPT!$F$6:$F$2793)=FALSE),--(RPT!$F$6:$F$2793=$A8),(RPT!CV$6:CV$2793),(RPT!$M$6:$M$2793=Overview!$B8))/$H$3

 

[mecheet]

thanks, i have tried the the below, but excel is saying there is an error in the formula.

=SUMPRODUCT(--(ISERROR((RPT!$F$6:$F$2793)=FALSE),--(RPT!$F$6:$F$2793=$A8),(RPT!CV$6:CV$2793),(RPT!$M$6:$M$2793=Overview!$B8))/$H$3

 

[yytsunamiyy]

Sorry was writing off the top of my head, try:

=SUMPRODUCT(--(ISERROR(RPT!$F$6:$F$2793)=FALSE),--(RPT!$F$6:$F$2793=$A8),(RPT!CV$6:CV$2793),--(RPT!$M$6:$M$2793=Overview!$B8))/$H$3

 

[mecheet]

thanks for your help, it now allows the formula, but its still returning NA

=SUMPRODUCT(--(ISERROR(RPT!$F$6:$F$2793)=FALSE),--(RPT!$F$6:$F$2793=$A8),(RPT!CV$6:CV$2793),--(RPT!$M$6:$M$2793=Overview!$B8))/$H$3

 

[yytsunamiyy]

does RPT!$M$6:$M$2793 contain Error Values?

If not step through the formula in formula evaluation to see where the Error occurs. I cant test at the mo, I'm on a machine w/o XL.

 

[mecheet]

no there are no error values in column M

how am i able to trace the error?

 

[yytsunamiyy]

Evaluate the formula step by step

 

[mecheet]

ive gone through the formula step by step and i cant see where it falls down.

does anyone have any other ideas?

 

[mecheet]

I have just gone through and for the errors in column F on the RPT tab i have just overwritten them with a value and the formula now works.

Does this help anyone to understand why it wasnt working previously?