Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I received this spreadsheet last year and thought it would be great to use year after year but it has an error in it. It is a spreadsheet that tracks vacation days, Personal holidays, Comp days, Sick days, etc. Column A Starting at row 9 going down is the months. Row 8 starting at column B going through AF are the number of days (not relevant to the formula but helps to keep track of what days it is tracking)

the formula reads like this (with may2 being a named range on the row that is giving me the #value) and FYI - Capital V means took the whole day as vacation and small v means it was a half day.

=sum(if(exact(may2,"V"),1,if(exact(may2,"v"),0.5,0)))

and the same response if I type it exactly as the row for April but insert the May named range. =sum(if(exact(may2,"V"),1,if(exact(may2,"v"),0.5)))

If I click on the row for April I see: {=sum(if(exact(apr1,"V"),1,if(exact(may2,"v"),0.5)))}

if I copy and paste it to the May row, I will get the results for obviously April since it uses the April named range. I am curious about the "{" and "}" that I see. I have never have seen these used before the = and at the end of the formula

I did think maybe there was something with the cells that are defined for the May row and have literally selected on each sell and hit delete. Still that same result

In addition if I test the working formula for another row in a separate cell - I did the same formula in another cell for April and it returned the same #value error.

I also thought maybe there was something in a hidden cell that is causing it so I made a new tab and typed everything in but still getting the same error.

Any insight on what it may be would be awesome.

share|improve this question
    
These are array formulas –  soandos Jul 2 '12 at 21:42

2 Answers 2

If you use SUMPRODUCT here you can avoid "array entry". Try this regular formula [revised]

=SUMPRODUCT(EXACT(may2,"v")/2+EXACT(may2,"V"))

share|improve this answer
    
The worked perfectly! Thank you!! –  Kim Jul 3 '12 at 14:38

To learn more about array formulas, see here. What is happening is that your range is named incorrectly. To see how it could be done, see here.

share|improve this answer
    
Ranges seemed to be named correctly as they work on other lines and I reviewed your link and this is how they were done. The formula provided below worked perfectly without changing how the range was named. Thank you for your answer. –  Kim Jul 3 '12 at 14:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.