Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
#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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
#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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|