PDA

View Full Version : Solved: how to sum rows in columns with Sun



lneilson
08-06-2008, 03:37 PM
Hi

how can I adjust this formula so it sums every row but only from the columns labled Sun. (the label is in the row 83) with the answer displayed in the respective row in the last column with data +1

'Get the last used column.
'Start looking in the last column.
For intCounter = intColCount To 1 Step -1
If Columns(intCounter).End(xlDown).Row < lngRowCount Then
intColLast = intCounter
blnFound = True
Exit For
ElseIf Columns(intCounter).End(xlDown).Row = lngRowCount Then
If Columns(intCounter).End(xlDown).Value <> vbNullString Then
intColLast = intCounter
blnFound = True
Exit For
End If
End If
Next intCounter

'Get the last used row within the collection of used columns.
lngRowLast = 1

For intCounter = 1 To intColLast
lngRowTemp = Columns(intCounter).Cells(lngRowCount, 1).End(xlUp).Row
If lngRowTemp > lngRowLast Then
lngRowLast = lngRowTemp - 2
End If
Next intCounter

For intCounter = 1 To lngRowLast
Columns(intColLast + 1).Cells(intCounter, 1).Formula = "=sum(A" & intCounter & ":" & Columns(intColLast).Cells(intCounter, 1).Address & ")"
Next intCounter

parttime_guy
08-08-2008, 07:37 PM
Are u looking for some customised time-sheet.

Bob Phillips
08-09-2008, 02:03 AM
You really need to think hard about what you want. This is the 3rd or 4th time you have asked the same question, you have much help, but I for one still have no clue exactly what you are trying to do.

Unless you can clearly explain what the purpose of this spreadsheet is, what you need to add to it, and any other factors that may be relevant, I fear that you will never resolve the matter.

mdmackillop
08-09-2008, 02:38 AM
I must agree with the last post. There is no data in Row 83. Why not highlight the cells you want added and also the cells where the result(s) should be placed. The problem does not look difficult, but I can't see what is really required.

Aussiebear
08-09-2008, 03:09 AM
In your workbook attached
1) there is data sitting within the range A1:J18, and nothing else to be seen. If this is the only data available then it begs the following questions

2) Will this data range change?
3) Are you trying to sum J1:J15, which appears to be the only data after (from a column perspective) the value "Sun" in cell I17?

How about posting another workbook with the preferred solution (even if its manually calculated) shown on Sheet 2?

lneilson
08-09-2008, 04:53 AM
thanks for all the replys i attached a new workbook hope it explains it clearer

thanks again

lneilson

lneilson
08-09-2008, 04:55 AM
pls see new reply

sheet 1 shows where I am now sheet 2 shows what I want

thanks
lneilson

Bob Phillips
08-09-2008, 05:12 AM
P11: =SUMIF($A$12:$N$12,P$12&".",$A11:$N11)

lneilson
08-09-2008, 05:24 AM
thanks for the repy

the problem is my data changes everyday so I would have redo the formula everyday. I posted the code I use to find the last column with data+1 and I need the solution to incorporate the formula so I don't have
to edit it everyday

thanks again

Aussiebear
08-09-2008, 05:35 AM
Which way does the new data go in? Across the columns or down the rows?

Bob Phillips
08-09-2008, 05:48 AM
Personally, I give up. I have asked you many times to explain what is going on, I have offered a variation on the same formula many times, and all you do is say is doesn't work, or the real situation is different. You have lamentably failed to help us to help you, so I am out.

mdmackillop
08-09-2008, 12:58 PM
Hi lneilson,
I'm closing your related threads on this subject. Please take the time to rephrase your question clearly, with a good example, and explaining how this is meant to work in real life. The posters here have answered literaly thousands of questions, and we do our best to comprehend when things are not clear. There comes a time though, when we have to move on.
Regards
MD

Aussiebear
08-09-2008, 01:51 PM
Actually since in the example, there's so little data, surely it would be just as simple to select a cell and hit the autosum (sigma ) function and adjust the range by selection.

parttime_guy
08-09-2008, 09:56 PM
I tried but .... do you think there could be an easier way?

BR

Aussiebear
08-10-2008, 12:27 AM
In an effort to reach a solution here, would the OP please post a workbook, which clearly shows which range he is looking to sum, (Colour the range), and indicate where this range is going likely be after the new data is entered.

The workbook as posted in Post #6 now has a new sheet. Please read this to gain a better understanding as to why we are posting the comments we are. We will await your responce.

lneilson
08-10-2008, 08:35 AM
across in columns

lneilson
08-10-2008, 08:44 AM
thanks for all your help

your on the right track the data in O column is the total of each row
now Im trying to total each row but only sun columns then I can go
and convert that formula to count each row only in mon columns and so
on through the rest of week

thanks again

lneilson

lneilson
08-10-2008, 09:07 AM
Ok sorry Im not getting my idea across will try a different way to commuicate it
thanks for all your help

lneilson

parttime_guy
08-10-2008, 11:05 AM
Knock knock Guz!

I give up!, but I will surely visit this post in a week's time.

Yo!
Happy Excelling :banghead:

Aussiebear
08-10-2008, 01:20 PM
Who is on the right track? If you are responding to my suggestions, then I'm assuming you want to sum every seven columns with the value in the eightth column? If Column O is on the right track, how come you didn't sum the rows after the first occurance of Sun in the data you provided (in your effort to clear up the confusion)?

Is there a reason why you don't insert the new data below the last row, (this way there is a set column for summing the rows)?

How does the new data get inserted? (By code or manually?)

Please consider formatting a sheet, with the correct layout, so that we can make a better effort at providing you with an answer to this issue?

parttime_guy
08-10-2008, 07:28 PM
.

Aussiebear
08-11-2008, 02:21 AM
parttime_guy, Please don't be flippant about this thread. I know that others have tried to give assistance in this matter, afterall I read all of the threads. It is part of my job.

Unless you want to be a little more "positive" in your assistance, please consider leaving this thread alone.