LinkBack URL
About LinkBacks
two days before my tax scheduel and my reports quit working.
I get #Value error.
here is the formuls: =SUMPRODUCT(--(MONTH(expences!$B$2:$B$9997)=1),--(YEAR(expences!$B$2:$B$9997)=2012),expences!$G$2:$G$9997)
two days before my tax scheduel and my reports quit working.
I get #Value error.
here is the formuls: =SUMPRODUCT(--(MONTH(expences!$B$2:$B$9997)=1),--(YEAR(expences!$B$2:$B$9997)=2012),expences!$G$2:$G$9997)
Seems both Year and Month are feeding from column B
If you found the solution to your question. Mark the thread as "Solved"
Thank everyone that helped you with a valid solution by clicking on their
There is no such thing as a problem, only a temporary lack of a solution
yes thats what I want if jan/2012 then sum. it was working before. is there a way to go back to apreviouly saved file, like yesterday? something happened this morning, and I got taxes on tue.
If there are TEXT entries in column B then the MONTH and/or YEAR function will fail and generate that error.
Try it like this:
=SUMPRODUCT(--(TEXT(expences!$B$2:$B$9997,"mmmyyyy")="Jan2012"),expences!$G$2:$G$9997)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
If there are any TEXT strings in column B, that will cause #VALUE! Errors in both the Month and year functions.
Try this instead
=SUMPRODUCT(--(TEXT(expences!$B$2:$B$9997,"mmmyyyy")="Jan2012"),expences!$G$2:$G$9997)
Ok, seems that there are invalid dates in column B. Either text or even a space will cause an error.
column B is formatted for mm/d/yy and dates are ok, column G is my shop supplies formatted for currency entries are all currency. I thing something happened at the sheet level in expences.
If column B contains only true Excel dates then this formula should return a result of 0:
=COUNTIF(expences!$B$2:$B$9997,"*")
That's counting cells that contain TEXT.
Are there any #VALUE! errors already in any of the ranges?
The error suggests that somewhere on range B2:B9997 there must be a text value or a space in a cell. Formatting cells will not solve it.
Did you tried the solutions mentioned above to the whole range (down to 9997) or select all blank cells till that range and press delete?
Last edited by Portuga; 04-07-2013 at 10:21 PM.
I tried your above mentioned formula on an empty cell and I get a return. it gives me a number but with an exclamation mark that says value refers to empty cell, but I do get a number.
my income report is working, I get a return on the reports but I also get refers to empty cell. I tell you what I did do this morning. I was adding older data to my logs then (older dated stuff) then I sorted by date so they would go to there proper order, I think this did something. Can I return to an earlyer saved file like yesterday somehow?
If you get a result other than 0 with this formula:
=COUNTIF(expences!$B$2:$B$9997,"*")
That means there are TEXT entries in the date range and those text entries will cause your orgiinal formula to return the #VALUE! error.
Do you have a backup copy of the file? That's the only way to go back to a previously saved version.
How can I find text entries, where do I put your countif formula??? no backup
All my fields have #VALUE in them, every cell in expence report 2012 that refers to the sheet expences where I log in all my expences has #Value. is there a way I can send you my spread sheet so you can inspect? I am at whits end tax in two days and expence report not working.
You can attach the file to a post.
I won't be able to look at it until tomorrow. I'm getting ready to call it a day.
wont let me post attachment file too large
Tony I got a (1) with the formula
=COUNTIF(expences!$B$2:$B$9997,"*") Also says with, (!) formula refers to empty cells. I may have deleted a row is there a way to find wich # row is missing? and put it back; what I mean expences!$B$2:$B$9997 may be missing a row?
Tony I got a (1) with the formula
=COUNTIF(expences!$B$2:$B$9997,"*") Also says with, (!) formula refers to empty cells. I may have deleted a row is there a way to find wich # row is missing? and put it back; what I mean expences!$B$2:$B$9997 may be missing a row?
Are you saying that you cannot trim a file down, so it fits post requirements?....if you can not do a basic 'save as' (new name); trim out irrelevant data, and find/replace some values ( for privacy/security ), how do you expect US to help you?
Some basics for file size reduction:
FIRST, use 'save as' "new file name",second, any sensitive/personal information, use the 'find/replace' options to change!
1> anything that does not effect the formula you are worried about, delete...
2> any conditional formatting not related to the question, delete....
3> any sheets not related to the problem, delete...
4> any formatting (ie- format cells->number ->date ) that can be made general format, do...
Make sure the basic formatting of ANY cells referenced to your concerned area(s) remain the same, this will help pinpoint areas where it is a simple formatting problem,
If you delete something, and errors show up related to the cells in question, either 'undo' or hard code some values into those cells (Includidng a few error values if that is what might return)
these all help reduce file size, and without something to look at; it is very hard to offer solutions
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
I want to thank everyone that responded to my problem! Fortunately I found the error problem, I had typed 11/31/12 for a date, there is no 11/31, changed it to 11/30/12 everything came back, no more #value error. Thanks. I still get a little green arrow on the top left corner of every cell in my reports that contain data, when I click the cells I get an (!) warning stating Formula referes to empty cells ??? what can I do to find out what???
OK, corrected the problem "formula refers to empty cells" I unchecked empty cell reporting box, I was getting this because I have not gotten to 9997 yet. In the future what can I do to the formula =SUMPRODUCT(--(MONTH(expences!$B$2:$B$9997)=1),--(YEAR(expences!$B$2:$B$9997)=2012),expences!$G$2:$G$9997) or any other so I dont get #VALUE error if I type a false date or something in column B ?
Again Thank you everyone!
Glad you got it straightened out.
In some formulas empty cells will evaluate as 0 and as a date will evaluate as January 1900.
However, in this particular application empty cells will have no effect on the calculation because you're looking for a specific month in a specific year, January 2012.
to make ERRORS "dis-appear", using your example formula:
Formula:
=IF(ISERROR(SUMPRODUCT(--(MONTH(expences!$B$2:$B$9997)=1),--(YEAR(expences!$B$2:$B$9997)=2012),expences!$G$2:$G$9997)),"",SUMPRODUCT(--(MONTH(expences!$B$2:$B$9997)=1),--(YEAR(expences!$B$2:$B$9997)=2012),expences!$G$2:$G$9997))
(NOTE_ this is for 2003 - users, 2007+, use this :
Formula:=IFERROR(SUMPRODUCT(--(MONTH(expences!$B$2:$B$9997)=1),--(YEAR(expences!$B$2:$B$9997)=2012),expences!$G$2:$G$9997),"")
thank you very much, thank you for all your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks