#Value Error

[Rana Gray]

Hey everyone! I need some help tweaking my formula to get rid of the #VALUE error it's generating.

Formula Goal: if range 1 (date format) is a Saturday, then sum the relevant cell in range 2 (number format) >> count how many of something is being done on Saturdays specifically out of all the days logged.

Formula: =SUMPRODUCT(--(WEEKDAY($B$4:$B$63)=7)*--$W$4:$W$63)

When I step into the formula I can see the problem are the blank cells in the range - I've used this formula before and haven't had an issue with this, but now I can't seem to find a workaround.

Any suggestions? (PS. I confirmed my observation by reducing the ranges to just the cells with data and it work fined)

 

[Kaper]

Are you sure that these cells are blank? Or mey be there are some non-printing characters? Or formulas, which return empty strings, like if(condition,value,"") etc.?

May be you could give a try to:

=SUMPRODUCT(IF(ISNUMBER($B$4:$B$63)*ISNUMBER($W$4:$W$63),(WEEKDAY($B$4:$B$63)=7)*$W$4:$W$63,0))

 

[kvsrinivasamurthy]

Try

=SUM(IFERROR((WEEKDAY($B$4:$B$63,1)=7)*$W$4:$W$63,0))

 

[Rana Gray]

Are you sure that these cells are blank? Or mey be there are some non-printing characters? Or formulas, which return empty strings, like if(condition,value,"") etc.?

May be you could give a try to:

=SUMPRODUCT(IF(ISNUMBER($B$4:$B$63)*ISNUMBER($W$4:$W$63),(WEEKDAY($B$4:$B$63)=7)*$W$4:$W$63,0))

Thank you this solved the problem, and to answer your question I made sure to clear cell contents and delete any phantom entries this did the trick! I appreciate you!

 

[Rana Gray]

Try

=SUM(IFERROR((WEEKDAY($B$4:$B$63,1)=7)*$W$4:$W$63,0))

Thank you this did work with one hiccup if I had additional entries in range W it would sum all of it vs just what met my condition in range B I appreciate the help!

 

[Kaper]

Glad we could help!
And thanks for feedback!

 

[kvsrinivasamurthy]

I did not understand what you mean. If problem is not solved, come with more and clear explanation. I will try to help.