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]

It should be easier to see where the formula errors out if you evaluate it step-by-step against a smaller data sample, for instance:

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

where in row 6 you have a value, in row 7 an NA error and in row 8 an #value error.

 

[mecheet]

thanks for that, it worked up until a point where there was an error, either #N/A or #VALUE

it appears to me that the formula is still looking at these in someway

 

[Aladin Akyurek]

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

Switch to a formula type that is more powerful...

If column F is expected to house numbers:

Control+shift+enter, not just enter...

=SUM(
    IF(ISNUMBER(RPT!$F$6:$F$2793),
    IF(RPT!$F$6:$F$2793=$A8,
    IF(RPT!$M$6:$M$2793=Overview!$B8,
      RPT!CV$6:CV$2793))))

Otherwise:

=SUM(
    IF(1-ISERROR(RPT!$F$6:$F$2793),
    IF(RPT!$F$6:$F$2793=$A8,
    IF(RPT!$M$6:$M$2793=Overview!$B8,
      RPT!CV$6:CV$2793))))

On Excel 2007 or later...

=SUMIFS(
    RPT!CV$6:CV$2793,
    RPT!$F$6:$F$2793=$A8,
    RPT!$M$6:$M$2793=Overview!$B8)

 

[mecheet]

thanks aladdin....the 2nd option worked perfectly.

 

[yytsunamiyy]

deleted

Thanks Aladin.

 

[Aladin Akyurek]

thanks aladdin....the 2nd option worked perfectly.

...
Thanks Aladin.

You are welcome. Thanks for providing feedback.