#VALUE!

[Guest]

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)),"",VLOOKUP(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.

 

[Peo Sjoblom]

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

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)),"",VLOOKUP(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.

 

[Don Guillett]

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
(e-mail address removed)

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)),"",VLOOKUP(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.