LinkBack URL
About LinkBacks
vonmunchy 
=SUM(IFERROW(--sheet1A2:D2,0))
This the formula that i got from one of the users but for some reason this formula works on sone cells wheb i drag it down.. any idea why and how to fix it?
=SUM(IFERROW(--sheet1A2:D2,0))
This the formula that i got from one of the users but for some reason this formula works on sone cells wheb i drag it down.. any idea why and how to fix it?
IferroW?
Is that a typo?
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Nope not a typo
You may need to upload a spreadsheet to give it some context. Right off hand IFERROW is spelled wrong but I'm not sure if that is just a typo here, if not it should be IFERROR.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
well you've got both mine and Martin's replies, change it to IFERROR and see if that works.
Yes my bad it was a typo, well the issue is this. When i drag down the formula in sheet 2, the formula does not work for some cells..
I'm not sure from your attachment what you are trying to sum. H3:J3 in sheet 1 only contains a date.
How were you planning to sum dates?
as you can see, i have dates in different columns and i wanted all dates under one column in a different sheet. I posted regarding this and that was the formula i got.
than i this issue popped up, it works on some cell..
as you can see, i have dates in different columns and i wanted all dates under one column in a different sheet. I posted regarding this and that was the formula i got.
than i this issue popped up, it works on some cell..
I think I see what you're doing..
On sheet 1, there will be only 1 date per row in columns H through J
And you want to find that date.
The problem is that many of your dates on Sheet1 are not really dates, they're just text strings.
Example, H4 shows 16/01/1982.
In US style dates (mm/dd/yyyy), that isn't a real date. There is no 16th month.
So in the formula on Sheet2, A2
=SUM(IFERROR(--Sheet1!H4:J4,0))
It is returning 0, because it was an error..
0 formatted as a Date is 1/0/1900
You need to correct the dates on Sheet1.
The ones that appear LEFT alligned are not really dates.
The ones that appear RIGHT alligned ARE real dates.
Hope that helps.
Yes your correct, well do you know an easy way to correct the errors because my original sheet contains 239,000 rows..
Jonmo1's observation is what I was working on and I agree that is what is hanging things up.
So is all of your data in dd/mm/yyyy format? Can you convert the data it comes from into mm/dd/yyyy format?
Well it depends on which date format your system has..
Some dates are real, and some aren't.
For any corrective action, we need to know which is which..
And it can be different from one pc using UK dates (dd/mm/yyyy) and another using US dates (mm/dd/yyyy).
So looking at YOUR pc, give a few examples of both..
A few that show LEFT alligned, and a few that show RIGHT alligned.
well i tried changing the format but it doesn't change..
Of coarse not.Originally Posted by vonmunchy
Changing the format of a cell only changes the way the contents of that cell is displayed.
It doesn't actually cange the value of the contents of the cell.
If the contents of the cell isn't really a date to begin with, changing the format won't fix it.
We have to fix the contents of the cell.
And we need to know which is which. See post #13.
This will take all the dates from columns H, I and J of Sheet 1 and put them in column A of sheet 2. There is however a problem with the dates on sheet1 in that some are real dates and others are text dates. Select column I and click on the Data tab, Text to Columns, Delimited, Next, Next, Select DATE, click FINISH. Repeat for columns I and J. This will convert all dates to real dates.
Formula:
=IF(ISNUMBER(Sheet1!H1),Sheet1!H1,IF(ISNUMBER(Sheet1!I1),Sheet1!I1,IF(ISNUMBER(Sheet1!J1),Sheet1!J1,"")))
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
This is duplicate of:
http://www.excelforum.com/excel-form...nt-column.html
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
This is your workbook with the dates converted and the formula applied
Thank you everyone for all your help. Really appriciate your quick responses
Thank you for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks