Want to highlight a helpful answer? Upvote!
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
Hello,
I am trying to use a function that will add values in a range that have a given date next to them. I keep getting a syntax error that I can't work out how to fix. Hopefully the image will explaine everything but if it's not clear Ill try and explaine further.
Thanks in advance.
Gary
iPad 2, iOS 8.3
Hi Gary,
SUMIFS() has a slightly peculiar syntax in that the condition needs to be in quotes. So if your condition was greater than 2 you would enter ">2" with the quotes. Since you are comparing to a cell it is a little more complicated. Your third argument would be:
"≥"&A4
your fifth:
"≥"&B4
I hope this is clear.
quinn
Hi Quinn,
Thank you for your help, again. I thought I had something to do with quotation marks. Then only problem I have is I don't know how to insert the quotation marks into the function on my iPad. When I have the function keyboard open on my ipad I can't see how I am able to put the quotations marks in. Can you help me with that too?
Thanking you
Gary
Hi Gary,
To enter "≥" just tap the "abc" key when you have your formula editor open. You will go to a screen where you can enter ≥. Come back and you will see "≥". Don't forget to use the &.
quinn
Thank you very much Quinn.
I also have another syntax error that I can't work out. When looking at the dates for the last weeks of the month I would like the "week end" date to always be the Sunday (this I already have). Unless the end of the month date falls in this week and then I'd like the "week end" date to be the date of the end of the month.
This is what I have but keeps returning a syntax error:
IF((A7+DURATION(weeks,6,hours,minutes,seconds,milliseconds))">"&EOMONTH(A3,0),EO MONTH(A3,0),A7+DURATION(weeks,6,hours,minutes,seconds,milliseconds))
Thanking you
Gary
Hi Gary,
This is what works for me.
=IF((A7+DURATION(,6))>EOMONTH(A3,0),EOMONTH(A3,0),A7+DURATION(,6))
I did say that SUMIFS() was weird. IF() did not like
">"&
prefers
>
The change I made in DURATION() is really just cosmetic. This formula still throws an error when there is not a date/time value in both the referenced cells.
quinn
Thank you very much Quinn. You have been very helpful.
I seem to have an issue with any function that I use comparison operators in. Do you know anywhere where I could reat up about them as all the documentation I have found is very basic. how exactly do you know when to use just >= or ">="& ? And what does the quotation and ampersand actually do when you enter it into a sumifs function?
Thanks again.
Gary
Hi Gary,
Besides this forum I get most of my help from the in app function descriptions you find by tapping the i next to the formula. Sometimes I have to read them pretty closely to understand what is not working or what I need to do. The description only talked about ">2" as an argument for SUMIFS. I knew if the cell reference was inside the quotes the formula would not see it for what it was. So I tried the ampersand.It seems all the SUMIF, CountIF type formulas need those quotes.
The ampersand is also called a concatenate operator. If I wanted the values is A1, B1, C1 together in a cell I could use the formula =A1&B1&C1. If they where words I wanted to put together with spaces I could use =A1&" "&B1&" "&C1.
hope this is helpful too.
quinn
Syntax error help
