Home |
Search |
Today's Posts |
#1
|
|||
|
|||
#VALUE! Problem
Using the below formula, #VALUE! is returned: =SUMPRODUCT(--(MONTH(Log!$A$2:$A$200)=MONTH(Stats!$D1)),--(Log!$D$2:$D$200=(Stats!$A3))) I think it is because Log! is linked to a 3rd sheet and contains ISBLANK as below: =IF(ISBLANK(Benefit_Plan_Review_Log.csv!$N2),"",Be nefit_Plan_Review_Log.csv!$N2) Is there anything I can do? Many thanks Andy -- andyp161 ------------------------------------------------------------------------ andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654 View this thread: http://www.excelforum.com/showthread...hreadid=383154 |
#2
|
|||
|
|||
Hi Andy,
Check all the ranges involved just in case they already contain the VALUE error. Check the ranges [Log!$A$2:$A$200] and [Stats!$D1] - potentially one of them or both contain values that appear as dates, but are in reality text strings. Regards, KL "andyp161" wrote in message ... Using the below formula, #VALUE! is returned: =SUMPRODUCT(--(MONTH(Log!$A$2:$A$200)=MONTH(Stats!$D1)),--(Log!$D$2:$D$200=(Stats!$A3))) I think it is because Log! is linked to a 3rd sheet and contains ISBLANK as below: =IF(ISBLANK(Benefit_Plan_Review_Log.csv!$N2),"",Be nefit_Plan_Review_Log.csv!$N2) Is there anything I can do? Many thanks Andy -- andyp161 ------------------------------------------------------------------------ andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654 View this thread: http://www.excelforum.com/showthread...hreadid=383154 |
#3
|
|||
|
|||
andyp161 Wrote: Using the below formula, #VALUE! is returned: =SUMPRODUCT(--(MONTH(Log!$A$2:$A$200)=MONTH(Stats!$D1)),--(Log!$D$2:$D$200=(Stats!$A3))) I think it is because Log! is linked to a 3rd sheet and contains ISBLANK as below: =IF(ISBLANK(Benefit_Plan_Review_Log.csv!$N2),"",Be nefit_Plan_Review_Log.csv!$N2) Is there anything I can do? Many thanks Andy Are the dates in Colmn N true date values? Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=383154 |
#4
|
|||
|
|||
andyp161 Wrote: Using the below formula, #VALUE! is returned: =SUMPRODUCT(--(MONTH(Log!$A$2:$A$200)=MONTH(Stats!$D1)),--(Log!$D$2:$D$200=(Stats!$A3))) I think it is because Log! is linked to a 3rd sheet and contains ISBLANK as below: =IF(ISBLANK(Benefit_Plan_Review_Log.csv!$N2),"",Be nefit_Plan_Review_Log.csv!$N2) Is there anything I can do? Many thanks Andy Sorry Andy! I just re-read your post and it looks like I misunderstood the situation. Try the following formula instead... =SUM(IF(Log!$A$2:$A$200<"",(MONTH(Log!$A$2:$A$200 )=MONTH(Stats!$D1))*(Log!$D$2:$D$200=Stats!$A3))) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=383154 |
#5
|
|||
|
|||
This works great - thanks. =SUM(IF(Log!$A$2:$A$200<"",(MONTH(Log!$A$2:$A$200 )=MONTH(Stats!B1))*(Log!$D$2:$D$200=Stats!$A3))) However, I am trying to extend the formula so that if the conditions are met, the formula goes on to count the values in a final column. I have tried the following: =SUM(IF(Log!$A$2:$A$200<"",(MONTH(Log!$A$2:$A$200 )=MONTH(Stats!B1))*(Log!$D$2:$D$200=Stats!$A3)*Log !$G$2:$G$200)) However, the above does not work; rather than counting the values in Log!$G$2:$G$200 if the previous conditions are met, it counts all values?? Many thanks Andy -- andyp161 ------------------------------------------------------------------------ andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654 View this thread: http://www.excelforum.com/showthread...hreadid=383154 |
#6
|
|||
|
|||
Does Column A contain 0 (zero) values? andyp161 Wrote: This works great - thanks. =SUM(IF(Log!$A$2:$A$200<"",(MONTH(Log!$A$2:$A$200 )=MONTH(Stats!B1))*(Log!$D$2:$D$200=Stats!$A3))) However, I am trying to extend the formula so that if the conditions are met, the formula goes on to count the values in a final column. I have tried the following: =SUM(IF(Log!$A$2:$A$200<"",(MONTH(Log!$A$2:$A$200 )=MONTH(Stats!B1))*(Log!$D$2:$D$200=Stats!$A3)*Log !$G$2:$G$200)) However, the above does not work; rather than counting the values in Log!$G$2:$G$200 if the previous conditions are met, it counts all values?? Many thanks Andy -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=383154 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
problem office assistant | Excel Discussion (Misc queries) | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) |