# Office 365 >  >  Date in Text format within a if statement

## peter9x9

a1 = number - a2 = the date   error message appears to have issue with ( after the use of TEXT?

=IF(A1>0;"OMRU  Accounts for the "&A1&" months ended"&Text(a2,"DD-MM-yyy""); " ")


also - if   cell has a value and that value can be changed - how do I write a formula to add a particular range ie   cell value is 3 - the sum of A1.c1 - cell value is 6 - A1.f1  - cell value is 9 - a1.p1 ) 

please help

----------


## Pete_UK

I don't understand how a value of 9 equates to column P, but I think you are looking for something like this:

=IFERROR(SUM(INDIRECT("A1:"&CHAR(A2+64)&"1")),"")

where A2 contains the number of columns you want to sum (max 26, which is equivalent to column Z).

For your first part, the TEXT function is referring to cell A2, but A2 contains the formula. It would help if you gave us a bit more detail.

Hope this helps.

Pete

----------


## peter9x9

Thanks for the reply - possibly did not explain the requirement clearly - 
p should have = I 
What I am trying to achieve is a formula that will add a number of cells - depending on the value a particular cell ie 
the value cell is  3  - then the formula will add cells a1+a2+a3, if the cell value is 6 it will add a1+a2+a3+a4+a5+a6  etc 

I have created this formula in the past - using if statement just cant recall 


=IF(A1>0;"OMRU  Accounts for the "&A1&" months ended"&Text(a2,"DD-MM-yyy""); " ")

What I am trying to achieve is that when cell A1 is numeric ( number of months ) - the value is inputted within the statement  & then I am looking at 
getting the date into the statement - that value would be inputted in cell a2 --- 
If I use the "&A2"& - the result is numeric 42674 :-(

I tried to format cell a2 as text but still get an error

----------


## Pete_UK

I would have thought that in Ireland you would use a comma rather than a semicolon to separate parameters in a formula, so try it this way:

=IF(A1>0,"OMRU Accounts for the "&A1&" months ended"&Text(a2,"DD-MM-yyy""), " ")

where A1 contains the number of months and A2 contains the date - the formula should be in a different cell.

As regards the other query, you now seem to want to add up cells in a column (i.e. vertically) rather than across a row - which is it to be?

Pete

----------


## peter9x9

Pete 

I did try comma - tried alot of variations - nothing prevailed - your suggestion is not working - same error 
query 2
Early morning have to wait for coffee to kick in - is should have read columns - A1+b1+c1+d1   etc 

thanking you

----------


## Pete_UK

Well, the first formula I gave you should work across columns.

It would help if you attached a sample Excel workbook. To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

----------


## peter9x9

pete 

file is attached I hope !!  sheet 1 has the date formula regarding the date cell B1 - sheet 2 is where the other formula is 

thanks 

Peter

----------


## Pete_UK

In B1 of the first sheet you should have this formula:

=IF(A1>0,"OMRU  Accounts for the "&A1&" months ended  "&TEXT(A2,"dd/mm/yyyy"))

I'm not exactly sure what you want in the second sheet, but if you want to sum the number of months given in V1, you can have this formula in V2:

=IFERROR(SUM(INDIRECT("E"&ROW()&":"&CHAR(V$1+64+5)&ROW())),"")

This will include column E (B/F value) and add the first 3 months to it. You can copy the formula into U2 to get 6 months added onto E2. If you don't want to include the E value then change it to F. Then you can copy both formulae down.

Hope this helps.

Pete

----------

