VLOOKUP/#NA/Sumif

[Robert_L.]

I have a table with the following headings: Phase, Date, PPM, RTY, FTY,l ocated in columns g-k, Rows 23 to 93.
Within PPM-FTY columns the data returned may have the value #NA, this is intential for I create graphs from this data. See table format below.
Phase Date PPM RTY FTY
Engineering 1/1/2005 3125 100% 100%
Evaluation 5/7/2005 6521 91% 96%
validation 5/5/2005 25000 62% 67%
Engineering 3/3/2005 #N/A #N/A #N/A

I am trying to perform a sum for given columns using the Phase column as the deciding criteria.

I have the following formula that will sum the columns even with #n/a: SUMIF(J24:J93,"<>#N/A"). I can also perform the sumif with a Vlookup on the cells without #n/a and get the appropriate results using the folloiwng formula:SUMIF($G24:$K$26,VLOOKUP($C$24,$G$24:$K$26,1,FALSE),($J$24:$J$26))

I need assistance in being able to perform a sumif-Vlookup to include the "<>#N/A" statement.

[olasa]

I'm not sure what you mean by "include the <>#N/A",
but this might be what you're looking for:

=SUMPRODUCT(($C$24=$G$24:$G$93)*(ISNUMBER($J$24:$J$93))*($J$24:$J$93))


Ola Sandström

[Robert_L.]

Unfortunately the SumProduct equation will read the #N/A and provide that for the answer as well.

The formula Sumif(Range,"<>#N/A") will treat the #N/A as a null value, providing me with the sum of the values in the column other than #N/A.

Thank you for answering though

[Domenic]

Try...

=SUM(IF(ISNUMBER($J$24:$J$93),($G$24:$G$93=$C24)*($J$24:$J$26)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!