#VALUE!
 

My cost column K looks for a CPT code in I5, then for that CPT code's cost in the "CPT" worksheet. If no CPT code, then column K is blank
=IF(ISNA(MATCH(I5,CPT!$A$2:$A$52,FALSE)),"",VLOOKU P(I5,CPT!$A$2:$C$52,3,FALSE)

I would like to sum column K based on a date column (G) for the entries in November
=SUMPRODUCT((MONTH(G2:G8)=11)*(K2:K8)
but I keep getting a #VALUE! error in that cell where the SUMPRODUCT formula is located because K5:K8 are blanks.

I have tried squiggly brackets around the SUMPRODUCT formula and using the ISNUMBER command:
=SUMPRODUCT((MONTH(G2:G8)=11)*ISNUMBER(K2:K8)), but I still get that value error. I have also tried pasting only the values into another column and summing that but it still gives me that value error. Help - thanks a million - really - I'm working at a nonprofit company.

#VALUE!
 

Then the blanks must come from a null string "",
try this amendment

=SUMPRODUCT((MONTH(G2:G8)=11)*(ISNUMBER(K2:K8)),K2 :K8)

--

Regards,

Peo Sjoblom


"Mary L." wrote in message
...
My cost column K looks for a CPT code in I5, then for that CPT code's cost
in the "CPT" worksheet. If no CPT code, then column K is blank:

=IF(ISNA(MATCH(I5,CPT!$A$2:$A$52,FALSE)),"",VLOOKU P(I5,CPT!$A$2:$C$52,3,FALS
E))

I would like to sum column K based on a date column (G) for the entries in
November:
=SUMPRODUCT((MONTH(G2:G8)=11)*(K2:K8))
but I keep getting a #VALUE! error in that cell where the SUMPRODUCT
formula is located because K5:K8 are blanks.

I have tried squiggly brackets around the SUMPRODUCT formula and using the
ISNUMBER command:
=SUMPRODUCT((MONTH(G2:G8)=11)*ISNUMBER(K2:K8)), but I still get that value
error. I have also tried pasting only the values into another column and
summing that but it still gives me that value error. Help - thanks a
million - really - I'm working at a nonprofit company.

#VALUE!
 

try
=SUMPRODUCT((MONTH(A1:A4)=11)*(ISNUMBER(C1:C4)),C1 :C4)

The trick is the comma at the end instead of *

--
Don Guillett
SalesAid Software

"Mary L." wrote in message
...
My cost column K looks for a CPT code in I5, then for that CPT code's cost
in the "CPT" worksheet. If no CPT code, then column K is blank:

=IF(ISNA(MATCH(I5,CPT!$A$2:$A$52,FALSE)),"",VLOOKU P(I5,CPT!$A$2:$C$52,3,FALS
E))

I would like to sum column K based on a date column (G) for the entries in
November:
=SUMPRODUCT((MONTH(G2:G8)=11)*(K2:K8))
but I keep getting a #VALUE! error in that cell where the SUMPRODUCT
formula is located because K5:K8 are blanks.

I have tried squiggly brackets around the SUMPRODUCT formula and using the
ISNUMBER command:
=SUMPRODUCT((MONTH(G2:G8)=11)*ISNUMBER(K2:K8)), but I still get that value
error. I have also tried pasting only the values into another column and
summing that but it still gives me that value error. Help - thanks a
million - really - I'm working at a nonprofit company.